How to select all but the first record

[Greyfleck]

I have a table of many records. I need to select all but the first one of a given type (Types being 2 chars)... any suggestions would be gratefully received.

 

[bborissov]

And how you know which is first?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Greyfleck]

I have the table ordered by type and ID (an autonumber) - I already have an SQl to select the first record ...

 

[bborissov]

SELECT Table.*
FROM Table
LEFT JOIN (SELECT Type, MIN(Id) AS Id 
                   FROM Table
           GROUP BY Type) Tbl1
     ON Table.Type = Tbl1.Type AND
        Table.Id = Tbl1.Id
WHERE Tbl1.Id IS NULL

BUT!!!!! If you have ONLY one record for that type you will get NO records at all, because that one record have MIN(Id).
Is that what you want?
If not, could you post some simple data and desired result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Greyfleck]

Thanks Borislav, I will try this tomorrow and, if I cannot get it to work, will send you some data. for the record, there will always be one record.

 

[bborissov]

Yes, but will they be at least two?
I said if you have ONLY one record that records will not appear in your recordset.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Greyfleck]

Sorry, yes at least two...

 

[Olaf Doschke]

I'd say it's much easier to select all records then, including the first, it does not get much slower and you could eleminate that record if you already have it.

Also, if you knwo the ID of the record you don't want, it would simply be

Select * from table where NOT (ID = knownID) order by ...

Bye, Olaf.

 

[bborissov]

After I slept I changed it a little:

SELECT Table.*
FROM Table
LEFT JOIN (SELECT Type, MIN(Id) AS Id
                   FROM Table
           GROUP BY Type
           HAVING COUNT(*) > 1) Tbl1
     ON Table.Type = Tbl1.Type AND
        Table.Id = Tbl1.Id
WHERE Tbl1.Id IS NULL

That way you should get and these records which have only one value
(not tested)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Greyfleck]

Borislav/OlafDoschke
Thanks for your help guys, in the end Olaf's example works so I will go with that one.
Many many Thanks....

Jon