DISTINCT() Question

Hello,

I currently have a SQL Statement:
SELECT ID, SignDescription FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

However, it shows several duplicates (which is expected, i do have several duplicates in the database), but I don't want duplicates, so I tried the following:

SELECT DISTINCT(SignDescription), ID FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

This shows the same exact results, which if I understand right, is expected also - my ID fields are all unique so of course this is going to happen.

If I do this (omit the ID field from the results):
SELECT DISTINCT(SignDescription) FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

I get all my unique signs with a unique SignDescription, but then I don't have the ID field...which I need.
But the problem is I need the ID field returned in the same results/recordset/whateveryoucallit.
Is this even possible?

thanks,
Jamieyes, it's possible, but you need to change the specs

;)

in english, what do you actually want to do?|||Ok,

I was worried that was a little confusing.

What I want to do is return all records in my db that have unique data in the signdescription field, and I want to use only the id field and the signdescription field. (and then of course there is the rest of the statement - WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription...but that part is already working for me)|||bear with me and let's go back and approach this thing from basic principles rather than from trying to browbeat the sql into shape, because until you understand the problem, writing sql is futile

suppose you have three rows, all with the same signdescription

which row do you want to keep? describe it in words|||k.
one row.|||select MAX(ID) as ID, SignDescription FROM Signs Group by SignDescription Order BY ID

or

Select MIN(ID) as ID, SignDescription FROM Signs Group by SignDescription Order BY ID

You can use this method if it doesn't matter which ID out of the duplicates you pull back. If it does matter which ID out of the duplicates you pull back then you need to reconsider the design of the table; which IMO is the best way to go.|||k.
one row.

You funny...which one

1 X
2 X
3 X

?

Read the hint sticky at the top of the thread|||KrustyDeKlown, it does not matter which id I pull, so I will try your example.

Brett, it doesn't matter which one, so 1 or 2 or 3 would work just the same for me. Thank you for being precise, I know you don't want to give a wrong answer. Which part of the hint sticky do I need to apply, (besides stating my question better) if any? (I did read it though...before my original post even)|||sample data, expected results, DDL

That part|||Thank you for being precise, I know you don't want to give a wrong answer.

Hey, I'm all for giving wong answers :D|||k.
one row.does it have to be a consistent row, or can it be a conglomeration

for example, suppose you have the following "duplicate" rows

4 fred 102 aaa y
5 fred 104 bbb x
6 fred 105 ccc z

there are three "fred" rows and you want to end up with just one

"yeah," i can hear you saying, "that's right, just one!"

would you accept this --

5 fred 102 ccc x

if so, then the sql is easy, but if not, then why not? because there never was a row like that!!!|||First off, I want to tell Brett that I didn't necessarily mean what I said about giving wrong answers. Actually I thought you and r937 were the same (so really I guess I should thank r937 for starting off being precise...but you too Brett ;) )- I didn't notice there were 2 people replying to me. Additionally, I didn't mean that one of you would give me a 'wrong' answer, I just meant, that you guys were really trying to understand my situation as to not give me something that didn't apply to me...or work for my situation.

Next, I'm not sure if I understood at first what Krusty meant when he said I can use his method if it doesn't matter which id I pull from the duplicates...is he saying that things are going to be mixed up, similar to what you are asking r937? If so, I guess it's acceptable in my current situation, as I'm not actually using the IDs right now (I just have to supply an 'id' to a function because it is a required parameter...but that's a separate subject). However, in the future, I might need to actually use that ID, which would mean that it would actually have to be 'correct'. Which brings me to my next question - what do you mean by

then why not? because there never was a row like that!!!

? I'm not sure I understand.

K, as far as the sample data, expected results, etc...if you guys want, I can give you more specific information. I just didn't realize that this was going to be this complicated when I first started this post. But I guess part of that is my fault for not being perfectly clear. ;)|||okay, here's one quick way to generate something useful

select SignDescription, max(X), max(Y), ... max(Z)
from thetable
group by SignDescription

where X, Y, Z are the other columns in the table

will that work?|||I think so. That's pretty much what I have now from what Krusty suggested. Here's my full SQL statement:

SELECT MAX(ID) AS ID, SignDescription FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' GROUP BY SignDescription ORDER BY SignDescription

So this will work as long as it returns the correct IDs I guess. It seems to be working great, although I haven't checked if it is giving the right IDs yet.