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?
8  7403 
"Richard" <ri***********@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, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1... "Richard" <ri***********@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, so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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" <ri***********@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, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...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" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1... "Richard" <ri***********@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, so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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 >" <ri***********@ntlworld.com<nospam> wrote in message
news:Sc*******************@newsfe5-gui.server.ntli.net...
"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1... "Richard" <ri***********@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, so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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" <RE****************************@acm.org> wrote in message
news:9u********************@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
--
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Alex Satrapa |
last post by:
I have a table from which I'm trying to extract certain information. For
historical reasons, we archive every action on a particular thing
('thing' is identified, funnily enough, by 'id'). So the...
|
by: Kelvin |
last post by:
Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.
I.E. Here's my...
|
by: mathilda |
last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are...
|
by: Fred Zuckerman |
last post by:
Can someone explain the difference between these 2 queries?
"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"
...
|
by: orekinbck |
last post by:
Hi There
Our test database has duplicate data:
COMPANYID COMPANYNAME
1 Grupple Group
2 Grupple Group
5 Grupple Group
3 Yada Inc
4 Yada...
|
by: monomaniac21 |
last post by:
hi!
is it possible to do the aforementioned query - selecting only distinct
in 1 col but retrieving all other cols at the same time.
regards
marc
|
by: burgensteen |
last post by:
Hi all,
New to this forum, so to start 'A big hello to you all'.
My question:
I have a drop down menu that is created from the username in my database but is distinct:
...
|
by: Techhead |
last post by:
I need to run a SELECT DISTINCT query across
multiple fields, but I need to add another field that is NON-DISTINCT
to my record set.
Here is my query:
SELECT DISTINCT lastname, firstname,...
|
by: zachster17 |
last post by:
Hi everyone,
First of all, sorry for the massive amount of SQL I am about to type. I have a database that has a "lives" table of insured employees and then another table (that links to the lives...
|
by: ggfota |
last post by:
Hi
I have a problem with SELECT DISTINCT query.
I'm trying to solve why it causes error 3464.
Guess that problem might be cause by one field.
The source table has text(17) field from which I...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
