SQL Server range select equivalent to SQL Anywhere

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server range select equivalent to SQL Anywhere

Author

Topic

ET
Starting Member

4 Posts

Posted - 2009-06-04 : 15:22:34

In SQL anywhere you can:

Select Top 100 Start At 101 ...

How do I do this in SQL Server?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 15:24:33

What does AT 101 do?

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-04 : 16:01:19

It gets the TOP 100 starting with an Value of 101.

For example if I do:

SELECT TOP 100 
	START AT 101
	ID,
	FirstName,
	LastName
FROM
	MyTable
ORDER BY
	ID

You would excpect to get back 100 rows with ID 101. (101, 102, 105, 109, etc... )Which would translate to SQL Server something like:

SELECT TOP 100 
	ID,
	FirstName,
	LastName
FROM
	MyTable
WHERE
	ID >= 101
ORDER BY
	ID

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 16:14:40

There is no day without learning something new...

Thank you Lamprey

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-04 : 16:40:17

My Pleasure. :)

ET
Starting Member

4 Posts

Posted - 2009-06-04 : 18:09:37

Thanks Lamprey.

Looks like I'm going to need to post the entire statement so you can help me figure out how to do this given my existing WHERE statement. This statement got me the first million records:

SELECT Top 1000000
    objID, ra, dec, cx, cy, cz, 
    sky_u, sky_g, sky_r, sky_i, sky_z, 
    u, g, r, i, z,
    psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z, 
    fiberMag_u, fiberMag_g, fiberMag_r, fiberMag_i, fiberMag_z,
    petroRad_u, petroRad_g, petroRad_r, petroRad_i, petroRad_z,
    isoA_u, isoA_g, isoA_r, isoA_i, isoA_z,
    isoB_u, isoB_g, isoB_r, isoB_i, isoB_z,
    offsetRa_u, offsetRa_g, offsetRa_r, offsetRa_i, offsetRa_z
    offsetDec_u, offsetDec_g, offsetDec_r, offsetDec_i, offsetDec_z,
    dered_u, dered_g, dered_r, dered_i, dered_z 
into
    mydb.Test1 from Galaxy
WHERE
    skyVersion = 1 and 
    probPSF = 0

Now I want the next million records, and so on, until I've reached the end of the table. I'd just get all records in one lump, but I have a 500mb limit of database space on the CASJOBS SQL Server:

http://cas.sdss.org/casjobs/

In order to get the next million records I have to download the first table I created (as CSV for my uses) and then "drop" the table so I can free up 500mb DB space for the next set of records. I'm not sure the record ID will work in this case because I'm not getting the records by ID, but by whether or not "skyVersion" = "1" AND "probPSF" = "0".

Is there a way?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-05 : 11:13:13

Kinda depends on how you want to specify chunks. Meaning, do you need to get exactly 1000000 rows at a time or does thast really matter? If not you could chunk it by id (assumning that ObjID is the PK):

SELECT 
    objID, ra, dec, cx, cy, cz, 
    sky_u, sky_g, sky_r, sky_i, sky_z, 
    u, g, r, i, z,
    psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z, 
    fiberMag_u, fiberMag_g, fiberMag_r, fiberMag_i, fiberMag_z,
    petroRad_u, petroRad_g, petroRad_r, petroRad_i, petroRad_z,
    isoA_u, isoA_g, isoA_r, isoA_i, isoA_z,
    isoB_u, isoB_g, isoB_r, isoB_i, isoB_z,
    offsetRa_u, offsetRa_g, offsetRa_r, offsetRa_i, offsetRa_z
    offsetDec_u, offsetDec_g, offsetDec_r, offsetDec_i, offsetDec_z,
    dered_u, dered_g, dered_r, dered_i, dered_z 
into
    mydb.Test1 from Galaxy
WHERE
    skyVersion = 1 
    and probPSF = 0
    AND ObjID > 0 and ObjID <= 100000
    
    
-- Next run change to
    AND ObjID > 100000 and ObjID <= 200000
	etc..

ET
Starting Member

4 Posts

Posted - 2009-06-05 : 17:00:51

The objID field is a concatenation four other fields. So it's not the record number. I'll see if the ID field exists (if so it's undocumented) and try that out. I'll get back to you.

ET
Starting Member

4 Posts

Posted - 2009-06-05 : 17:22:42

I got the following error when I tried to use the field ID in place of ObjID in the WHERE statement: Invalid column name 'ID'.

I know I can't rely on ObjID for row number. I just checked the six (thought it was four) other fields used to create ObjID, and none of these represent the record number. Is there a default field name for the record numbers resulting from a query that I could use in place of ObjID or ID?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:16:33

quote:
Originally posted by ET

I got the following error when I tried to use the field ID in place of ObjID in the WHERE statement: Invalid column name 'ID'.

I know I can't rely on ObjID for row number. I just checked the six (thought it was four) other fields used to create ObjID, and none of these represent the record number. Is there a default field name for the record numbers resulting from a query that I could use in place of ObjID or ID?

the reason for error is that you dont have a column called ID in your table.
there's no such default field which stores record numbers. either you need to have a identity field in your table which generates record numbers automatically on insert and use it or if you're using sql 2005 or later you can use window function ROW_NUMBER() to generate this number based on order of some table fields

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources