Selecting highest value fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting highest value fields

Author

Topic

rogersql
Starting Member

3 Posts

Posted - 2005-10-27 : 10:41:37

This sounds so simple, but its driving me mad

System Microsft SQL Server 2000

I need to find out how to select the highest value fields against multiples held in another field. I hope to end up with a list held in a view with unique values of case_id by the highest table_id or latest date.

From this table

table_id case_id event_id eventdate
4930 5001 7 2005-10-27 00:00:00
4920 5001 7 2005-10-27 00:00:00
3950 5001 7 2005-10-20 00:00:00
1735 5001 7 2005-10-03 00:00:00
362 5001 7 2005-09-19 00:00:00
306 5001 7 2005-09-15 00:00:00
1913 5002 7 2005-10-04 00:00:00
1922 5003 7 2005-10-04 00:00:00
3737 5004 7 2005-10-19 00:00:00
1927 5004 7 2005-10-04 00:00:00
2207 5005 7 2005-10-05 00:00:00
1933 5006 7 2005-10-04 00:00:00
1939 5007 7 2005-10-04 00:00:00
1944 5008 7 2005-10-04 00:00:00
1949 5009 7 2005-10-04 00:00:00

to this view

table_id case_id event_id eventdate
4930 5001 7 2005-10-27 00:00:00
1913 5002 7 2005-10-04 00:00:00
1922 5003 7 2005-10-04 00:00:00
3737 5004 7 2005-10-19 00:00:00
2207 5005 7 2005-10-05 00:00:00
1933 5006 7 2005-10-04 00:00:00
1939 5007 7 2005-10-04 00:00:00
1944 5008 7 2005-10-04 00:00:00
1949 5009 7 2005-10-04 00:00:00

This morning have tried many forms of select queries using Group Order Max and sub_queries

Any help would be gratefully appreciated.

Thanks

Roger

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-27 : 10:52:00

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(table_id int, case_id int, event_id int, eventdate datetime)
GO

INSERT INTO myTable99(table_id, case_id, event_id, eventdate)
SELECT 4930, 5001, 7, '2005-10-27 00:00:00' UNION ALL
SELECT 4920, 5001, 7, '2005-10-27 00:00:00' UNION ALL
SELECT 3950, 5001, 7, '2005-10-20 00:00:00' UNION ALL
SELECT 1735, 5001, 7, '2005-10-03 00:00:00' UNION ALL
SELECT 362 , 5001, 7, '2005-09-19 00:00:00' UNION ALL
SELECT 306 , 5001, 7, '2005-09-15 00:00:00' UNION ALL
SELECT 1913, 5002, 7, '2005-10-04 00:00:00' UNION ALL
SELECT 1922, 5003, 7, '2005-10-04 00:00:00' UNION ALL
SELECT 3737, 5004, 7, '2005-10-19 00:00:00' UNION ALL
SELECT 1927, 5004, 7, '2005-10-04 00:00:00' UNION ALL
SELECT 2207, 5005, 7, '2005-10-05 00:00:00' UNION ALL
SELECT 1933, 5006, 7, '2005-10-04 00:00:00' UNION ALL
SELECT 1939, 5007, 7, '2005-10-04 00:00:00' UNION ALL
SELECT 1944, 5008, 7, '2005-10-04 00:00:00' UNION ALL
SELECT 1949, 5009, 7, '2005-10-04 00:00:00'
GO

SELECT *
FROM myTable99 o
WHERE eventdate = (SELECT MAX(eventdate)
FROM myTable99 i
WHERE i.case_id = o.case_id)
ORDER BY case_id
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rogersql
Starting Member

3 Posts

Posted - 2005-10-27 : 12:21:51

Hi

Thanks for your swift reply. It is very similar to what I was trying to do and works to some degree.

However I still have a problem in that it is producing duplicate rows where the eventdate is the same. Give that we have no time stamp in eventdate is there anyway of incorporating table_id as this incremented for every new entry and therefore is an indication of age. I need the last one entered so that I have unique rows based on case_id.

Thanks again for your time.

Roger

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-27 : 12:25:04

Unless you have an IDENTITY column or a date added column, how would you even know which one was the "last entered"

You can't, is the short answer.

The order of data in a database is meaningless....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rogersql
Starting Member

3 Posts

Posted - 2005-10-27 : 12:29:34

Hi

table_id in my example identity column with seeding set to 1

Roger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources