problem with SQL query, DISTINCT isn't quite right.

[HotMadras]

I have a problem with an SQL query I'm trying to use. It's supposed to return all the fields of the set of records where TRIPNUM is unique. i.e. if I have the following table:

ID     TRIPNUM     TRIPNAME
1      BS0207      Blackpool
2      BS0204      Alton Towers
3      BS0207      Blackpool

and what I want from the query is:

ID     TRIPNUM     TRIPNAME
1      BS0207      Blackpool
2      BS0204      Alton Towers

I want only one instance of the Blackpool trip (the others turn up because the database hasn't been terribly well designed and there's a bunch of fields which should really be in a separate table buggering things up).

If I use DISTINCT, I only get the TRIPNUM data, unfortunately this is no use because I need the entire record.

Is there a way to do what I want with SQL? I'm guessing there must be because it doesn't seem to be a particularly stupid thing to want to do. I just want to take the first instance of every unique TRIPNUM and ignore the rest, generating a recordset that just contains records with unique TRIPNUMs.

Somebody please help. I'm getting ready to throw things.

 

[CFDude]

Can you do something like:

Select t1.TripID, t1.TripName, t1.TripNum
From TheTable t1
Where
t1.TripID = (Select MAX(t2.TripID) from TheTable t2 where t1.TripName = t2.TripName)

 

[HotMadras]

I'm not sure I understand what you are suggesting. I've only got one table. I want to get a recordset out of it which contains just one record per TRIPNUM (as illustrated above, I hope that makes sense).

 

[CFDude]

This only uses one table. Replace the references of TheTable with the table you are pulling it from. (The t1 and t2 are just aliases to the same table)

This just refines the query to select only the max trip id where the name is the same. In looking at your message again, I noticed you wanted the first record ID, so you may want to change the query to:


Select t1.TripID, t1.TripName, t1.TripNum
From TheTable t1
Where
t1.TripID = (Select MIN(t2.TripID) from TheTable t2 where t1.TripName = t2.TripName)

 

[Mighty]

Have you tried:

Select DISTINCTROW * FROM myTable Mise Le Meas,

Mighty

 

[jo4sony]

I'm not a SQL expert but can offer this: one of my DBA's had a fit when one of my consultants coded a SELECT DISTINCT in his ASP. DISTINCT locks the database from other users (and DBAs). Instead, use GROUP BY and you should see the desired results.

Select * from table
where blah blah blah
GROUP BY tripnum, tripname

Hope it helps.

jo