October 24, 2003 at 3:59 pm
I have a checklist that I need to store in a database. The checklist will not necessarily change every item regularly. So, I wanted to store it as follows:
id int primary key
item_no int
item_yesno char(1)
item_date datetime
This would look like this with data in it:
id item_no item_yesno item_date
1 1 Y 2003-10-10
2 2 Y 2003-10-10
3 3 Y 2003-10-10
4 4 Y 2003-10-10
5 5 Y 2003-10-10
6 2 N 2003-10-15
Now, what I cannot figure out is how do I get a query to only return the items with the most current date for a given item_no. I have tried:
select item_no, item_yesno, max(item_date) group by item_no, item_yesno
but I get
item_no item_yesno item_date
1 Y 2003-10-10
2 Y 2003-10-10
3 Y 2003-10-10
4 Y 2003-10-10
5 Y 2003-10-10
2 N 2003-10-15
I also tried
select item_no, max(item_date) from checklist group by item_no
but this gives
item_no item_date
1 2003-10-10
2 2003-10-15
3 2003-10-10
4 2003-10-10
5 2003-10-10
which is right but it does not have the item_yesno data in it. I would like to be able to do this with one query rather than have to store the date for each item_no and then go back through with one query per item_no to get the yesno data.
Any suggestions?
Ed
October 24, 2003 at 5:00 pm
Perhaps something like this?
SELECT c.Item_No, c.Item_YesNo, c.Item_Date
FROM CheckList c JOIN
(SELECT Item_No, MAX(Item_Date) Item_Date
FROM Checklist
GROUP BY Item_No) m ON c.Item_No = m.Item_No AND c.Item_Date = m.Item_Date
ORDER BY c.Item_No
--Jonathan
--Jonathan
October 28, 2003 at 4:41 pm
quote:
Perhaps something like this?
SELECT c.Item_No, c.Item_YesNo, c.Item_Date
FROM CheckList c JOIN
(SELECT Item_No, MAX(Item_Date) Item_Date
FROM Checklist
GROUP BY Item_No) m ON c.Item_No = m.Item_No AND c.Item_Date = m.Item_Date
ORDER BY c.Item_No
This works great! Now, the next step is that I need to be able to run this on multiple SQL server flavors (SQL Server, Oracle, etc.) so it needs to be less SQL Server specific and as it is it will not run on any other flavor of SQL.
Any ideas on how to do this in ANSI SQL?
Ed
October 29, 2003 at 5:29 am
quote:
quote:
Perhaps something like this?
SELECT c.Item_No, c.Item_YesNo, c.Item_Date
FROM CheckList c JOIN
(SELECT Item_No, MAX(Item_Date) Item_Date
FROM Checklist
GROUP BY Item_No) m ON c.Item_No = m.Item_No AND c.Item_Date = m.Item_Date
ORDER BY c.Item_NoThis works great! Now, the next step is that I need to be able to run this on multiple SQL server flavors (SQL Server, Oracle, etc.) so it needs to be less SQL Server specific and as it is it will not run on any other flavor of SQL.
Any ideas on how to do this in ANSI SQL?
Ed
That is ANSI SQL-92 syntax. Some implementations (not Oracle) might require "AS" before the aliases or, perhaps, "INNER JOIN" rather than just "JOIN". Plus you may need the ";" terminator (which is understood but not needed in T-SQL).
--Jonathan
--Jonathan
August 29, 2006 at 1:50 pm
I was reviewing this need once again and find myself on a database that does not support subqueries and cannot get it upgraded at this time.
I have spent the last hour or two trying to find another way to re-write this without a subquery and using some form of inner or outer join and cannot get this to work.
How would I go about re-writing this query WITHOUT the subquery? Is there an easy way to do this?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply