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?
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)
select MAX(ID) as ID, SignDescription FROM Signs Group by SignDescription Order BY ID
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.
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)
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.