| |
|
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.
|
 |
|
01-31-06, 04:10
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
|
|
Max/Min dates from records
|
|
Code:
+----+------------+
| id | date |
+----+------------+
| 0 | 2006-01-01 |
| 1 | 2006-01-10 |
| 2 | 2006-01-15 |
| 3 | 2006-01-05 |
| 4 | 2006-01-03 |
| 5 | 2006-01-02 |
+----+------------+
I'd like to get the id for the last date.
Maybe by doing something like that:
Code:
SELECT
t.id
FROM
table AS t
WHERE
t.date = MAX(t.date)
This query is not valid.
How can I do it ?
|
|
01-31-06, 04:16
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
|
|
Do I have to do
Code:
SELECT
t.id
FROM
table AS t
ORDER BY
t.date DESC
LIMIT 1
?
|
|
01-31-06, 05:11
|
|
Registered User
|
|
Join Date: Dec 2005
Location: Arnhem, Gld, NL
Posts: 21
|
|
|
|
i thought this could work,..
Code:
select id from (select max(date) from table)
|
|
01-31-06, 05:28
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,258
|
|
your could try the "top" clause in the select
with an order by <mycolumn> DESC;
select top 1 <mydatecolumn> from <mytable> order by <mydatecolumn> DESC;
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton 
|
|
01-31-06, 09:03
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
|
|
Are you sure that TOP is implemented in MySQL ?
Ok now let's imagine that content
Code:
+----+------------+------+
| id | date | user |
+----+------------+------+
| 0 | 2006-01-01 | 5 |
| 1 | 2006-01-10 | 6 |
| 2 | 2006-01-15 | 2 |
| 3 | 2006-01-05 | 2 |
| 4 | 2006-01-03 | 6 |
| 5 | 2006-01-02 | 0 |
+----+------------+------+
How can we get the id for the last date for each user.
Which should give us:
Code:
+----+------+
| id | user |
+----+------+
| 0 | 5 |
| 1 | 6 |
| 2 | 2 |
| 5 | 0 |
+----+------+
Now I'm not sure that this query works:
Code:
SELECT DISTINCT
t.id AS "id",
t.user AS "user"
FROM
table AS t
ORDER BY
t.date
|
|
03-29-07, 09:02
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
|
|
Isn't the question clear ?
|
|
03-29-07, 11:51
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
steer clear of distinct whereever possible:
SELECT id,user
FROM <table> t
WHERE t.date=(SELECT MAX(date) FROM <table> WHERE id=t.id)
WHAT IS THE NAME OF YOUR TABLE? IT'S NOT "TABLE" IS IT?
in the sql above replace <table> with the actual name of your table.
|
|
03-30-07, 05:34
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
|
|
Ok so we are obliged to do a sub-select to get this.
|
|
03-30-07, 05:58
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
You're not obliged to do anything of course. I'm just writing out your query another way ... Using a correlated subquery.
I think another way to do this would be to use a GROUP BY clause:
SELECT id,user,MAX(date)
FROM <table> t
GROUP BY date,user
|
|
03-30-07, 06:02
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
Actually having tested that query above I can say it doesn't work... Actually neither of the queries I supplied above work correctly. It's always handy to try these things out 
|
|
03-30-07, 06:07
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
SELECT id,user,MAX(date)
FROM <table> t
GROUP BY user
ORDER BY t.date
That works ok and doesn't have a distinct in it...
|
|
03-30-07, 06:08
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by gtk
Ok so we are obliged to do a sub-select to get this.
|
what's the problem, are you not on 4.1 yet? if not, why not?
Code:
select t1.id
, t1.date
, t1.user
from daTable as t1
inner
join daTable as t2
on t2.user = t1.user
group
by t1.id
, t1.date
, t1.user
having t1.id = max(t2.id)
|
|
03-30-07, 06:10
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by aschk
That works ok and doesn't have a distinct in it...
|
no, it only appears to work okay
it's actually invalid, and the value of the id is unpredictable
see GROUP BY and HAVING with Hidden Fields
|
|
03-30-07, 06:12
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
Here is the CORRECT correlated query:
SELECT id,user
FROM <table> t
WHERE t.date=(SELECT MAX(date) FROM <table> where `user`=t.`user`);
That's what I should have written the first time. I was comparing on id and it should have been user... silly me.
Edit: Using a BTREE index on user and date (ALTER TABLE ADD INDEX(`user`,`date`)) improves correlated subquery causing ONLY 1 row lookup.
|
Last edited by aschk; 03-30-07 at 06:22.
|
03-30-07, 06:27
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
Quote:
select t1.id
, t1.date
, t1.user
from daTable as t1
inner
join daTable as t2
on t2.user = t1.user
group
by t1.id
, t1.date
, t1.user
having t1.id = max(t2.id)
|
This query gives the wrong result. I believe he is looking for the ID and User where the date is greatest. Not where the ID is greatest.
|
|
| 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
>
Max/Min dates from records
