DISTINCT QUERY

**Erland Sommarskog** · Jul 20 '05, 03:06 AM

Re: DISTINCT QUERY

"Richard" <richard.spare@ ntlworld.com (nospam>) writes:[color=blue]
> This is probably easy but I can't work it out. I have this statement
>
> SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceRe ference.Name
> FROM dbo.CIF_Departu res INNER JOIN
> dbo.CIF_PlaceRe ference ON
> dbo.CIF_Departu res.EndPoint >= dbo.CIF_PlaceRe ference.PlaceID
> ORDER BY dbo.CIF_PlaceRe ference.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_PlaceRe ference.Name,db o.CIF_Departure s.StartTime
> FROM dbo.CIF_Departu res INNER JOIN
> dbo.CIF_PlaceRe ference ON
> dbo.CIF_Departu res.EndPoint>= dbo.CIF_PlaceRe ference.PlaceID
> ORDER BY dbo.CIF_PlaceRe ference.Name
>
> ... I get multiple place names.[/color]

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_Departu res d
JOIN dbo.CIF_PlaceRe ference 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

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Richard** · Jul 20 '05, 03:06 AM

Re: DISTINCT QUERY

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns94A426F 0918EYazorman@1 27.0.0.1...[color=blue]
> "Richard" <richard.spare@ ntlworld.com (nospam>) writes:[color=green]
> > This is probably easy but I can't work it out. I have this statement
> >
> > SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceRe ference.Name
> > FROM dbo.CIF_Departu res INNER JOIN
> > dbo.CIF_PlaceRe ference ON
> > dbo.CIF_Departu res.EndPoint >= dbo.CIF_PlaceRe ference.PlaceID
> > ORDER BY dbo.CIF_PlaceRe ference.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_PlaceRe ference.Name,db o.CIF_Departure s.StartTime
> > FROM dbo.CIF_Departu res INNER JOIN
> > dbo.CIF_PlaceRe ference ON
> > dbo.CIF_Departu res.EndPoint>= dbo.CIF_PlaceRe ference.PlaceID
> > ORDER BY dbo.CIF_PlaceRe ference.Name
> >
> > ... I get multiple place names.[/color]
>
> 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_Departu res d
> JOIN dbo.CIF_PlaceRe ference 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/techinf...2000/books.asp[/color]

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(e ndpoint 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 tmpPlaceReferen ce(PlaceID char(12), Name char(50))
INSERT INTO tmpPlaceReferen ce(PlaceID , Name ) VALUES ('1', 'Here')
INSERT INTO tmpPlaceReferen ce(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

**Erland Sommarskog** · Jul 20 '05, 03:06 AM

Re: DISTINCT QUERY

"Richard" <richard.spare@ ntlworld.com (nospam>) writes:[color=blue]
> 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(e ndpoint 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 tmpPlaceReferen ce(PlaceID char(12), Name char(50))
> INSERT INTO tmpPlaceReferen ce(PlaceID , Name ) VALUES ('1', 'Here')
> INSERT INTO tmpPlaceReferen ce(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[/color]

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(cha r(8), d.starttime, 108))
FROM dbo.tmpPlaceRef erence pr
LEFT JOIN dbo.tmpDepartur es 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

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**David Portas** · Jul 20 '05, 03:06 AM

Re: DISTINCT QUERY

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_TIMEST AMP,14))
FROM tmpPlaceReferen ce 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_TIMESTA MP.

--
David Portas
SQL Server MVP
--

**Richard** · Jul 20 '05, 03:06 AM

Re: DISTINCT QUERY

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns94A5166 2B5A8Yazorman@1 27.0.0.1...[color=blue]
> "Richard" <richard.spare@ ntlworld.com (nospam>) writes:[color=green]
> > 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(e ndpoint 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 tmpPlaceReferen ce(PlaceID char(12), Name char(50))
> > INSERT INTO tmpPlaceReferen ce(PlaceID , Name ) VALUES ('1', 'Here')
> > INSERT INTO tmpPlaceReferen ce(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[/color]
>
> 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(cha r(8), d.starttime, 108))
> FROM dbo.tmpPlaceRef erence pr
> LEFT JOIN dbo.tmpDepartur es 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/techinf...2000/books.asp[/color]

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** · Jul 20 '05, 03:06 AM

Re: DISTINCT QUERY

"Richard >" <richard.spare@ ntlworld.com<no spam> wrote in message
news:Sct2c.2405 8$gC2.23350@new sfe5-gui.server.ntli .net...[color=blue]
>
> "Erland Sommarskog" <sommar@algonet .se> wrote in message
> news:Xns94A5166 2B5A8Yazorman@1 27.0.0.1...[color=green]
> > "Richard" <richard.spare@ ntlworld.com (nospam>) writes:[color=darkred]
> > > This goes some way to helping except instead of the MAX(d.starttime ),[/color][/color][/color]
i[color=blue][color=green][color=darkred]
> > > need the the nearest record to the current time.
> > >
> > > CREATE TABLE tmpDepartures(e ndpoint 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 tmpPlaceReferen ce(PlaceID char(12), Name char(50))
> > > INSERT INTO tmpPlaceReferen ce(PlaceID , Name ) VALUES ('1',[/color][/color][/color]
'Here')[color=blue][color=green][color=darkred]
> > > INSERT INTO tmpPlaceReferen ce(PlaceID , Name ) VALUES ('2',[/color][/color][/color]
'There')[color=blue][color=green][color=darkred]
> > >
> > > 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[/color]
> >
> > 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(cha r(8), d.starttime, 108))
> > FROM dbo.tmpPlaceRef erence pr
> > LEFT JOIN dbo.tmpDepartur es 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/techinf...2000/books.asp[/color]
>
> 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
>
>[/color]
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'

**David Portas** · Jul 20 '05, 03:06 AM

Re: DISTINCT QUERY

> table tmpDepartures has a new column called 'StartPoint'. I need to
refinne[color=blue]
> the resulting rows to a specific 'StartPoint'[/color]

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_TIMEST AMP,14))
FROM tmpPlaceReferen ce AS P

Erland's solution:

SELECT pr.Name, MIN(convert(cha r(8), d.starttime, 108))
FROM dbo.tmpPlaceRef erence pr
LEFT JOIN dbo.tmpDepartur es 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
--

**Richard** · Jul 20 '05, 03:07 AM

Re: DISTINCT QUERY

Thats great guys. Thanks very much.

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:9uCdnWYkyP Yc89fdRVn-gQ@giganews.com ...[color=blue][color=green]
> > table tmpDepartures has a new column called 'StartPoint'. I need to[/color]
> refinne[color=green]
> > the resulting rows to a specific 'StartPoint'[/color]
>
> 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_TIMEST AMP,14))
> FROM tmpPlaceReferen ce AS P
>
> Erland's solution:
>
> SELECT pr.Name, MIN(convert(cha r(8), d.starttime, 108))
> FROM dbo.tmpPlaceRef erence pr
> LEFT JOIN dbo.tmpDepartur es 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
> --
>
>[/color]

DISTINCT QUERY

DISTINCT QUERY

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment