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.

DISTINCT QUERY

This is probably easy but I can't work it out. I have this statement

SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
FROM dbo.CIF_Departures INNER JOIN
dbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint
= dbo.CIF_PlaceReference.PlaceID
ORDER BY dbo.CIF_PlaceReference.Name

This results in a column of placenames which is OK. There are also multiple
'time of day' values against each placename however I only want to return
the one nearest to the current time. If I do this...

SELECT DISTINCT TOP 100 PERCENT
dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
FROM dbo.CIF_Departures INNER JOIN
dbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint
= dbo.CIF_PlaceReference.PlaceID
ORDER BY dbo.CIF_PlaceReference.Name

... I get multiple place names.

Any ideas?"Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> This is probably easy but I can't work it out. I have this statement
> SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
> FROM dbo.CIF_Departures INNER JOIN
> dbo.CIF_PlaceReference ON
> dbo.CIF_Departures.EndPoint >= dbo.CIF_PlaceReference.PlaceID
> ORDER BY dbo.CIF_PlaceReference.Name
> This results in a column of placenames which is OK. There are also
> multiple 'time of day' values against each placename however I only want
> to return the one nearest to the current time. If I do this...
> SELECT DISTINCT TOP 100 PERCENT
> dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
> FROM dbo.CIF_Departures INNER JOIN
> dbo.CIF_PlaceReference ON
> dbo.CIF_Departures.EndPoint>= dbo.CIF_PlaceReference.PlaceID
> ORDER BY dbo.CIF_PlaceReference.Name
> ... I get multiple place names.

Of course. If you would get disctinct names, how do you think SQL Server
would be able to find out which StartTimes you want? DISTINCT applies
to all columns.

Your requirement is not wholly clear, so I present a simple solution,
you simply get the latest starttime:

SELECT pr.Name, MAX(d.StartTime)
FROM dbo.CIF_Departures d
JOIN dbo.CIF_PlaceReference pr ON d.EndPoint = pr.PlaceID
GROUP BY pr.Name
ORDER BY pr.Name

If this does meet your requirement, please post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o Desired output from this sample.

This reduces the amount of guessing that anyone that helps you has
to do, and it also makes it simple to provide a tested solution.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94A426F0918EYazorman@.127.0.0.1...
> "Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> > This is probably easy but I can't work it out. I have this statement
> > SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
> > FROM dbo.CIF_Departures INNER JOIN
> > dbo.CIF_PlaceReference ON
> > dbo.CIF_Departures.EndPoint >= dbo.CIF_PlaceReference.PlaceID
> > ORDER BY dbo.CIF_PlaceReference.Name
> > This results in a column of placenames which is OK. There are also
> > multiple 'time of day' values against each placename however I only want
> > to return the one nearest to the current time. If I do this...
> > SELECT DISTINCT TOP 100 PERCENT
> > dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
> > FROM dbo.CIF_Departures INNER JOIN
> > dbo.CIF_PlaceReference ON
> > dbo.CIF_Departures.EndPoint>= dbo.CIF_PlaceReference.PlaceID
> > ORDER BY dbo.CIF_PlaceReference.Name
> > ... I get multiple place names.
> Of course. If you would get disctinct names, how do you think SQL Server
> would be able to find out which StartTimes you want? DISTINCT applies
> to all columns.
> Your requirement is not wholly clear, so I present a simple solution,
> you simply get the latest starttime:
> SELECT pr.Name, MAX(d.StartTime)
> FROM dbo.CIF_Departures d
> JOIN dbo.CIF_PlaceReference pr ON d.EndPoint = pr.PlaceID
> GROUP BY pr.Name
> ORDER BY pr.Name
> If this does meet your requirement, please post:
> o CREATE TABLE statements for your tables.
> o INSERT statements with sample data.
> o Desired output from this sample.
> This reduces the amount of guessing that anyone that helps you has
> to do, and it also makes it simple to provide a tested solution.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks Erland

This goes some way to helping except instead of the MAX(d.starttime), i need
the the nearest record to the current time.

CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
'10:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
'10:00:00')
CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')

If the time now is 21:59. I need a query that returns:
Here 22:00:00
There 22:00:00

If the time now is 22:01

Here Null
There Null

Regards
Richard|||"Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> This goes some way to helping except instead of the MAX(d.starttime), i
> need the the nearest record to the current time.
> CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> '10:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> '10:00:00')
> CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
> INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')
> If the time now is 21:59. I need a query that returns:
> Here 22:00:00
> There 22:00:00
> If the time now is 22:01
> Here Null
> There Null

Your definition of "nearest record" still eludes me. From the narrative,
it is not obvious why 22:00:00 should not be returned when current time
is 22:01. But the sample output makes it clear what you want.

Here is a query that almost gives the desired output. Almost, because
it is impossible to return 22:00:00 for There, as this time is not given
for There.

declare @.now datetime
select @.now = '20040306 21:59'

SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
FROM dbo.tmpPlaceReference pr
LEFT JOIN dbo.tmpDepartures d
ON d.endpoint = pr.PlaceID
AND d.starttime > convert(char(8), @.now, 108)
GROUP BY pr.Name
ORDER BY pr.Name

The convert stuff is need because there is no time data type in SQL
Server. SQL Server accepts '10:00:00' for input to a datetime value,
but that actually means '19000101 10:00:00'. Convert takes a couple
of format codes for datetime values, 108 is for time only.

Note that if @.now is 23:59 and there is a departure at midnight, that
depature will not be listed.

Finally a note about your script: it's a good idea to run it and check
before you post. I can tell that you hadn't, because the datetime(8)
gave me a syntax error.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Earliest StartTime greater than or equal to now for each Placeid:

SELECT P.name,
(SELECT MIN(starttime)
FROM tmpDepartures
WHERE endpoint = P.placeid
AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
FROM tmpPlaceReference AS P

This assumes you are only interested in times not dates. DATETIME stores
both but apparently you are using the "default" date value of 1900-01-01. If
the date is in fact significant then just replace the CONVERT expression
with CURRENT_TIMESTAMP.

--
David Portas
SQL Server MVP
--|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94A51662B5A8Yazorman@.127.0.0.1...
> "Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> > This goes some way to helping except instead of the MAX(d.starttime), i
> > need the the nearest record to the current time.
> > CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > '22:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > '22:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> > '10:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> > '10:00:00')
> > CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
> > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')
> > If the time now is 21:59. I need a query that returns:
> > Here 22:00:00
> > There 22:00:00
> > If the time now is 22:01
> > Here Null
> > There Null
> Your definition of "nearest record" still eludes me. From the narrative,
> it is not obvious why 22:00:00 should not be returned when current time
> is 22:01. But the sample output makes it clear what you want.
> Here is a query that almost gives the desired output. Almost, because
> it is impossible to return 22:00:00 for There, as this time is not given
> for There.
> declare @.now datetime
> select @.now = '20040306 21:59'
> SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> FROM dbo.tmpPlaceReference pr
> LEFT JOIN dbo.tmpDepartures d
> ON d.endpoint = pr.PlaceID
> AND d.starttime > convert(char(8), @.now, 108)
> GROUP BY pr.Name
> ORDER BY pr.Name
> The convert stuff is need because there is no time data type in SQL
> Server. SQL Server accepts '10:00:00' for input to a datetime value,
> but that actually means '19000101 10:00:00'. Convert takes a couple
> of format codes for datetime values, 108 is for time only.
> Note that if @.now is 23:59 and there is a departure at midnight, that
> depature will not be listed.
> Finally a note about your script: it's a good idea to run it and check
> before you post. I can tell that you hadn't, because the datetime(8)
> gave me a syntax error.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Despite my errors with the datetime in CREATETABLE and the fact I got the
INSERT wrong also..should have been:

INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('2','22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('1,'10:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('2','10:00:00')

... you did well to give me the answer (sorry about that).

All seems to work well .. Many thanks for your help.

Regards
Richard|||"Richard >" <richard.spare@.ntlworld.com<nospam> wrote in message
news:Sct2c.24058$gC2.23350@.newsfe5-gui.server.ntli.net...
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns94A51662B5A8Yazorman@.127.0.0.1...
> > "Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> > > This goes some way to helping except instead of the MAX(d.starttime),
i
> > > need the the nearest record to the current time.
> > > > CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > > '22:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > > '22:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> > > '10:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> > > '10:00:00')
> > > CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> > > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1',
'Here')
> > > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2',
'There')
> > > > If the time now is 21:59. I need a query that returns:
> > > Here 22:00:00
> > > There 22:00:00
> > > > If the time now is 22:01
> > > > Here Null
> > > There Null
> > Your definition of "nearest record" still eludes me. From the narrative,
> > it is not obvious why 22:00:00 should not be returned when current time
> > is 22:01. But the sample output makes it clear what you want.
> > Here is a query that almost gives the desired output. Almost, because
> > it is impossible to return 22:00:00 for There, as this time is not given
> > for There.
> > declare @.now datetime
> > select @.now = '20040306 21:59'
> > SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> > FROM dbo.tmpPlaceReference pr
> > LEFT JOIN dbo.tmpDepartures d
> > ON d.endpoint = pr.PlaceID
> > AND d.starttime > convert(char(8), @.now, 108)
> > GROUP BY pr.Name
> > ORDER BY pr.Name
> > The convert stuff is need because there is no time data type in SQL
> > Server. SQL Server accepts '10:00:00' for input to a datetime value,
> > but that actually means '19000101 10:00:00'. Convert takes a couple
> > of format codes for datetime values, 108 is for time only.
> > Note that if @.now is 23:59 and there is a departure at midnight, that
> > depature will not be listed.
> > Finally a note about your script: it's a good idea to run it and check
> > before you post. I can tell that you hadn't, because the datetime(8)
> > gave me a syntax error.
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp
> Despite my errors with the datetime in CREATETABLE and the fact I got the
> INSERT wrong also..should have been:
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('2','22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('1,'10:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('2','10:00:00')
> ... you did well to give me the answer (sorry about that).
> All seems to work well .. Many thanks for your help.
> Regards
> Richard
>
Now I want to add a new variable

table tmpDepartures has a new column called 'StartPoint'. I need to refinne
the resulting rows to a specific 'StartPoint'|||> table tmpDepartures has a new column called 'StartPoint'. I need to
refinne
> the resulting rows to a specific 'StartPoint'

You mean just an extra predicate in the WHERE clause?

My solution:

SELECT P.name,
(SELECT MIN(starttime)
FROM tmpDepartures
WHERE endpoint = P.placeid
AND startpoint = /* something */
AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
FROM tmpPlaceReference AS P

Erland's solution:

SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
FROM dbo.tmpPlaceReference pr
LEFT JOIN dbo.tmpDepartures d
ON d.endpoint = pr.PlaceID
AND d.startpoint = /* something */
AND d.starttime > convert(char(8), @.now, 108)
GROUP BY pr.Name
ORDER BY pr.Name

If that doesn't answer your question, please post revised DDL, sample data
and show your required result.

--
David Portas
SQL Server MVP
--|||Thats great guys. Thanks very much.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:9uCdnWYkyPYc89fdRVn-gQ@.giganews.com...
> > table tmpDepartures has a new column called 'StartPoint'. I need to
> refinne
> > the resulting rows to a specific 'StartPoint'
> You mean just an extra predicate in the WHERE clause?
> My solution:
> SELECT P.name,
> (SELECT MIN(starttime)
> FROM tmpDepartures
> WHERE endpoint = P.placeid
> AND startpoint = /* something */
> AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
> FROM tmpPlaceReference AS P
> Erland's solution:
> SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> FROM dbo.tmpPlaceReference pr
> LEFT JOIN dbo.tmpDepartures d
> ON d.endpoint = pr.PlaceID
> AND d.startpoint = /* something */
> AND d.starttime > convert(char(8), @.now, 108)
> GROUP BY pr.Name
> ORDER BY pr.Name
> If that doesn't answer your question, please post revised DDL, sample data
> and show your required result.
> --
> David Portas
> SQL Server MVP
> --