Forums > Server-Side Scripting

Subject: Select Distinct - Return all columns

Posted By	Message
UnderDog Sun 1st Jan '10 20:34 user	I need to return distinct records from a multicolumn select statement and didn't know if this was possible. SELECT DISTINCT st_id, id, st_last, st_first FROM interventions Obviously, this doesn't work but I'd like to be able to generate the id, st_last, st_first for Distinct st_id's. I hope that makes sense. TIA - Duval
suehami Mon 2nd Jan '10 18:52 user	Hi I think you need to look at using GROUP BY heres a discussion on the subject: http://www.webmasterworld.com/... more here: http://www.w3schools.com/SQl/sql_groupby.asp or using a sub query might work http://www.tizag.com/sqlTutorial/sqlsubqueries.php
UnderDog Mon 2nd Jan '10 19:27 user	Thanks for the references and I had a read through the group by and can't get it to fly. The former seems to indicate that I'd need to create a "temporary table" and the latter suggests that it must be part of an aggregate function... sum or whatever. I try the following and get "You tried to execute a query that does not include the specified expression 'st_last' as part of an aggregate function." Which is what one would expect from the information provided at w3schools. SELECT DISTINCT (st_id), st_last, st_first, id FROM interventions GROUP BY st_id
suehami Tue 3rd Jan '10 17:27 user	How about you try this: Select (everything you need) then GROUP BY st_ID Dunno, might work :o) Sorry can't be much help, this needs the Rob touch :o) Sue
UnderDog Wed 4th Jan '10 1:26 user	Thanks for having a look but I get the same error... does not include fieldnamehere as part of an aggregate function.
UnderDog Sat 7th Feb '10 19:57 user	I've either worked around... or let go my other recent questions (thanks for the help) but still need to get this one sorted if possible so if anyone has any ideas I'd appreciate it. To refresh, I need to be able to pull several fields from a table around a unique identifier. I've tried DISTINCT but then it only gives me the distinct field and group by but keep getting an error in that it's not an aggregate function. In this I need to pull the following fields from the table interventions: id st_id st_last st_first intv_date But so that the st_id is not duplicated (if there are several records for the st_id field it would only select the user once by that field). I'm pasting my recordset below. <% Dim rs_rec_intv Dim rs_rec_intv_cmd Dim rs_rec_intv_numRows Set rs_rec_intv_cmd = Server.CreateObject ("ADODB.Command") rs_rec_intv_cmd.ActiveConnection = MM_con_interventions_STRING rs_rec_intv_cmd.CommandText = "SELECT * FROM interventions ORDER BY id DESC" rs_rec_intv_cmd.Prepared = true Set rs_rec_intv = rs_rec_intv_cmd.Execute rs_rec_intv_numRows = 0 %>
suehami Mon 2nd Feb '10 22:21 user	I've just had what might be a brain wave, then again it might not work :o) how about you do this SELECT your fields FROM your table GROUP BY st_ID somehow it seems like it ought to work .... Sue
UnderDog Mon 2nd Feb '10 4:20 user	Thanks Sue and I'll try it and report but every time I've tried the group by it gives me an error as not being part of an aggregate function.
UnderDog Mon 2nd Feb '10 4:27 user	Yup... same: Microsoft JET Database Engine error '80040e21' You tried to execute a query that does not include the specified expression 'id' as part of an aggregate function.
UnderDog Wed 4th Feb '10 7:10 user	Thanks again for your time Sue and just wanted to let you know that it got sorted. Asked around at a bunch of places and got the statement below which is working well (in case anyone else might run into the need to do similar). select st_id,st_last,st_first,id from interventions where id in (select max(id) from interventions group by st_id) order by id DESC
suehami Wed 4th Feb '10 14:20 user	Hi Glad you got it sorted, interesting the IN qualifier, never even thought of that :o) At least I got some of it right ;-) Sue