|
This is my query now:
SELECT ID,
DAY
FROM (SELECT ROWNUM as CAL_ROWNUM,
DAY AS MAX_CALENDAR_DAY
FROM (SELECT DAY
FROM CALENDAR
WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
AND CALENDAR.IS_WORKING_DAY= 'y'
ORDER BY CALENDAR.DAY)
WHERE ROWNUM <= (SELECT MAX(NR_OF_DAYS)
FROM WORKCENTER
WHERE ID IN (1, 2, 6, 7, 9))
)
INNER JOIN WORKCENTER
ON NR_OF_DAYS = CAL_ROWNUM
WHERE ID IN (1, 2, 6, 7, 9)
And it works, yeah! Thanks guys.
Greetings
|
|
|
|
|
Hi all,
I'm running SQL CE on Wince 5.0, and am finding that about every 40 seconds or so that the whole system freezes then carries on. This happens at precisely the time when the timestamp on the .SDF file changes, so I am assuming that it is freezing when SQL is updating the database file. Is there any way to prevent this or specify when it happens as it's interfering with other operations on the board?
TIA!
Edit - Fixed: on-board Flash is far, far too slow.
modified on Tuesday, August 3, 2010 7:56 AM
|
|
|
|
|
I have a table with 5000 rows
I want to get the first 1000 rows in a single select query
and the next 1001 to 5000 rows in another select query.
A help from somebody will be deeply appreciated.
modified on Tuesday, August 3, 2010 11:34 PM
|
|
|
|
|
Which database server and version?
|
|
|
|
|
|
The first one is easy enough.
SELECT TOP 1000 * FROM MYTABLE
The other one could probably be done like this assuming you have a Primary Key MYTABLEID (or some other unique key):
SELECT *
FROM MYTABLE
WHERE MYTABLEID IN (SELECT TOP 5000 MYTABLEID FROM MYTABLE)
AND MYTABLEID NOT IN (SELECT TOP 1000 MYTABLEID FROM MYTABLE)
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thank you for the valuable information. Yes it worked
|
|
|
|
|
|
Although this is how it used to be done pre 2005, in SQL Server 2005 there was the ROW_NUMBER function which enabled much more efficient paging queries to be written.
|
|
|
|
|
Quite similar to Oracle then, which still lacks the TOP function.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I Have two Tables like Code and Details...
In Code(SrNo,Name) Table Records Like A,B,C etc
In Details(SrNo,CodeName)Table looks like
SrNo A B C
1 0 1 0
2 0 0 0
3 1 0 0
4 0 0 0
5 0 0 1
6 1 1 1
7 0 0 0
If I add another Name in Code Table Like 'D'. Select query for Details table also contain column 'D'.
So Select query like this
DECLARE @Code NVARCHAR(MAX)
SET @Code = (SELECT ('CONVERT(VARCHAR,'+Name+ ')'''+Name+''',') FROM Code ORDER BY SrNo FOR XML PATH(''))
SET @Code = SUBSTRING(@Code,0,LEN(@Code))
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT '+@Code+' FROM Details'
EXEC (@SQL)
Output:I need to check any '1' in columns and srno in order by without mentioning column names. It has to be like below. How can I do in SqlServer2005
SrNo A B C
6 1 1 1
1 0 1 0
3 1 0 0
5 0 0 1
2 0 0 0
4 0 0 0
7 0 0 0
|
|
|
|
|
You can refer to columns by ordinal as select foo from bar order by 1, 2 .
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
you mean to say by Srno. If so its not my requirement.
|
|
|
|
|
So elucidate as it did appear that's what you asked for.
In any case you can use select foo from bar order by 2 or 3 or 1000
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
modified on Monday, August 2, 2010 6:26 AM
|
|
|
|
|
Has anyone used this product? Is there anything out there free and better?
I did some research but chose this product because;
1) It was free
2) Fairly lightweight
3) C# API
4) GNU General Public License which if I understand correctly allows me to just about anything with it. Any gotchas there if I go commercial with my product?
5) Integrates with VS2005 and VS2008
6) Ease of use (Seems to be drop-dead easy?)
Guess I answered my own question unless there is something that just absolutely sucks about it?
Thanks,
Mike
|
|
|
|
|
Never heard of it; got a link?
|
|
|
|
|
|
I just read through their Wikipedia page. It looks like it only persists objects, it seems it doesn't support SQL, it might not allow you to use queries to create different subsets of data.
Looking at their site now...
|
|
|
|
|
PIEBALDconsult wrote: it seems it doesn't support SQL
That's one of the things I didn't like about it, but they have a nice Native Query alternative that looks to be pretty sophisticated.
I've only been working with it for a couple of hours and have found a couple of things I don't like about it but can live with.
Also the documentation is decent so that helps a lot, at least for me.
|
|
|
|
|
If it does what you need, then use it.
|
|
|
|
|
PIEBALDconsult wrote: If it does what you need, then use it.
It looks like it will do what I want but I haven't used many embedded DB's so the only reason I posted was so if I got 3/4 of the way down the line and started finding problems, restrictions, etc. then maybe someone with more experience could save me the frustration and point me to a better EDB.
Anyway thanks for your input
|
|
|
|
|
for an embedded DB it's simply the fastest approach.
Not saying it's faster than SQL Lite or SQL Compact just that it's a lot
faster to create the app. No need to map or use ORM tools in order to fill
the gap between the ObjectModel and the DB Layer/Model.
I used it a lot as a small embedded DB and it's more than fine.
I wouldn't risk buying it and using it as a DB Server.
But for small/local/embedded DBs it just works.
I bug
|
|
|
|
|
Thanks that's exactly the feedback I was looking for. I'm just using it as a local DB as I didn't want users of application I am developing to have to install SqlExpress or other server just to run this app.. Just needed something fast, lite and easy to learn/use.
Thanks for the input,
Mike
|
|
|
|
|
you're welcome
I bug
|
|
|
|
|
Hi!
I"ve to add values to a field but the previous values should be deleted. Which query do I've to use?
|
|
|
|