| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-07-08, 10:32
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 27
|
|
|
distinct values - why won't this work?
|
|
I have a table with fields UNIT,LATITIUDE,LONGITUDE,and TIME
data is like so...
10 41 95 1100
10 41 94 1030
16 40 95 1025
10 41 94 1015
15 41 93 1007
16 40 94 0955
I want to select the last entry (most current) in the table for each UNIT.
I tried SELECT DISTINCT UNIT,LATITUDE,LONGITUDE,TIME but I am only getting one UNIT's data, not each UNIT's most current data.
HELP! thanks,
|
|
10-07-08, 11:00
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here are two examples:
Code:
------------------------------ Commands Entered ------------------------------
WITH
TestData(unit, latitude, longitude, time) AS (
VALUES
(10,41,95,1100)
,(10,41,94,1030)
,(16,40,95,1025)
,(10,41,94,1015)
,(15,41,93,1007)
,(16,40,94,0955)
)
/* End of test data */
SELECT unit, latitude, longitude, time
FROM TestData A
WHERE time
= (SELECT MAX(time)
FROM TestData B
WHERE b.unit = a.unit
)
;
------------------------------------------------------------------------------
UNIT LATITUDE LONGITUDE TIME
----------- ----------- ----------- -----------
10 41 95 1100
16 40 95 1025
15 41 93 1007
3 record(s) selected.
------------------------------ Commands Entered ------------------------------
WITH
TestData(unit, latitude, longitude, time) AS (
VALUES
(10,41,95,1100)
,(10,41,94,1030)
,(16,40,95,1025)
,(10,41,94,1015)
,(15,41,93,1007)
,(16,40,94,0955)
)
/* End of test data */
SELECT unit, latitude, longitude, time
FROM (SELECT A.*
, ROWNUMBER() OVER(PARTITION BY unit
ORDER BY time DESC) rn
FROM TestData A
) S
WHERE rn = 1
;
------------------------------------------------------------------------------
UNIT LATITUDE LONGITUDE TIME
----------- ----------- ----------- -----------
10 41 95 1100
15 41 93 1007
16 40 95 1025
3 record(s) selected.
|
|
10-07-08, 18:54
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 27
|
|
|
|
Ok, now I also have a DATE field in the table. That SQL statement won't work anymore because the MAX(TIME) needs to be selected only from the MAX(DATE). any help would be greatly appreciated.
|
|
10-08-08, 00:19
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 7
|
|
You can modify one of the queries given above to
Code:
WITH
TestData(unit, latitude, longitude, time, date) AS (
VALUES
(10,41,95,1100,'26/07/2008')
,(10,41,94,1030,'26/07/2008')
,(16,40,95,1025,'27/07/2008')
,(10,41,94,1015,'27/07/2008')
,(15,41,93,1007,'28/07/2008')
,(16,40,94,0955,'28/07/2008')
)
/* End of test data */
SELECT unit, latitude, longitude, time, date
FROM (SELECT A.*
, ROWNUMBER() OVER(PARTITION BY unit
ORDER BY date desc, time DESC) rn
FROM TestData A
) S
WHERE rn = 1
;
just look at the data in bold..... the data will now be sorted first by date descending and then by time in descending order..... this would satisfy your requirement
ResultSet for your reference
Quote:
10 41 94 1015 27/07/2008
15 41 93 1007 28/07/2008
16 40 94 955 28/07/2008
|
|
Last edited by v3nom; 10-08-08 at 00:23.
|
10-08-08, 00:41
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here are three ways.
Code:
------------------------------ Commands Entered ------------------------------
WITH
TestData(unit, latitude, longitude, date, time) AS (
VALUES
(10,41,95,20081007,1100)
,(10,41,94,20081008,1030)
,(16,40,95,20081007,1025)
,(10,41,94,20081008,1015)
,(15,41,93,20081007,1007)
,(16,40,94,20081008,0955)
)
/* End of test data */
SELECT unit, latitude, longitude, date, time
FROM (SELECT A.*
, ROWNUMBER() OVER(PARTITION BY unit
ORDER BY date DESC, time DESC) rn
FROM TestData A
) S
WHERE rn = 1
;
------------------------------------------------------------------------------
UNIT LATITUDE LONGITUDE DATE TIME
----------- ----------- ----------- ----------- -----------
10 41 94 20081008 1030
15 41 93 20081007 1007
16 40 94 20081008 955
3 record(s) selected.
------------------------------ Commands Entered ------------------------------
WITH
TestData(unit, latitude, longitude, date, time) AS (
VALUES
(10,41,95,20081007,1100)
,(10,41,94,20081008,1030)
,(16,40,95,20081007,1025)
,(10,41,94,20081008,1015)
,(15,41,93,20081007,1007)
,(16,40,94,20081008,0955)
)
/* End of test data */
SELECT a.*
FROM TestData A
INNER JOIN
(SELECT unit
, MAX(date) AS max_date
FROM TestData B
GROUP BY unit
) MD
ON md.unit = a.unit
AND md.max_date = a.date
WHERE a.time
= (SELECT MAX(time)
FROM TestData C
WHERE c.unit = a.unit
AND c.date = md.max_date
)
;
------------------------------------------------------------------------------
UNIT LATITUDE LONGITUDE DATE TIME
----------- ----------- ----------- ----------- -----------
10 41 94 20081008 1030
15 41 93 20081007 1007
16 40 94 20081008 955
3 record(s) selected.
------------------------------ Commands Entered ------------------------------
WITH
TestData(unit, latitude, longitude, date, time) AS (
VALUES
(10,41,95,20081007,1100)
,(10,41,94,20081008,1030)
,(16,40,95,20081007,1025)
,(10,41,94,20081008,1015)
,(15,41,93,20081007,1007)
,(16,40,94,20081008,0955)
)
/* End of test data */
SELECT a.*
FROM TestData A
INNER JOIN
LATERAL
(SELECT date, time
FROM TestData B
WHERE b.unit = a.unit
ORDER BY
date DESC, time DESC
FETCH FIRST 1 ROW ONLY
) B
ON b.date = a.date
AND b.time = a.time
;
------------------------------------------------------------------------------
UNIT LATITUDE LONGITUDE DATE TIME
----------- ----------- ----------- ----------- -----------
10 41 94 20081008 1030
15 41 93 20081007 1007
16 40 94 20081008 955
3 record(s) selected.
|
|
10-08-08, 08:07
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Another way.
Code:
------------------------------ Commands Entered ------------------------------
WITH
TestData(unit, latitude, longitude, date, time) AS (
VALUES
(10,41,95,20081007,1100)
,(10,41,94,20081008,1030)
,(16,40,95,20081007,1025)
,(10,41,94,20081008,1015)
,(15,41,93,20081007,1007)
,(16,40,94,20081008,0955)
)
/* End of test data */
SELECT a.*
FROM TestData A
WHERE (date, time) IN /* or "= ANY" */
(SELECT date, time
FROM TestData B
WHERE b.unit = a.unit
ORDER BY
date DESC, time DESC
FETCH FIRST 1 ROW ONLY
)
;
------------------------------------------------------------------------------
UNIT LATITUDE LONGITUDE DATE TIME
----------- ----------- ----------- ----------- -----------
10 41 94 20081008 1030
15 41 93 20081007 1007
16 40 94 20081008 955
3 record(s) selected.
|
|
10-08-08, 08:13
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
(Deleted, because of duplicated post.)
|
Last edited by tonkuma; 10-08-08 at 09:41.
|
10-08-08, 14:53
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 27
|
|
The first example using JOIN worked perfectly. thank you very much for your help!!!!
|
|
10-13-08, 07:34
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
Yet another idea: combine your DATE and TIME values into a TIMESTAMP and apply MAX() to that.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
distinct values - why won't this work?
