How do I use the "DISTINCT" command

[michaaal]

I use the following SQL command in my ASP webpage...

SELECT DISTINCT CarList,CarListID FROM Model

My problem is that I would like for "CarList" to be distinct and not
"CarListID". How can I fix this problem?

[Steve Kass]

You must then specify what value of CarListID you wish to retrieve for a
given value of CarList. For example,

select CarList, CarListID
from Model M1
where CarListID = (
select top 1 CarListID
from Model M2
where M2.CarList = M1.CarList
order by ...
)

SK

michaaal wrote:

I use the following SQL command in my ASP webpage...

SELECT DISTINCT CarList,CarListID FROM Model

My problem is that I would like for "CarList" to be distinct and not
"CarListID". How can I fix this problem?

[David Portas]

Possibly you can use GROUP BY to get the result you want:

SELECT carlist, MIN(carlistid) AS carlistid
FROM Model
GROUP BY carlist

This is the easiest way to return the mimimum or maximum value of Carlistid
for each Carlist. If you have other columns that you want to include then
you probably need something more like the example that Steve Kass posted.

--
David Portas
------------
Please reply only to the newsgroup
--

[michaaal]

>

SELECT carlist, MIN(carlistid) AS carlistid
FROM Model
GROUP BY carlist

This is the easiest way to return the mimimum or maximum value of Carlistid for each Carlist. If you have other columns that you want to include then
you probably need something more like the example that Steve Kass posted.

--
David Portas
------------
Please reply only to the newsgroup
--

Awesome. Thanks guys.