Solved

Query to return unique row based on column 1

Posted on 2013-12-24
7
425 Views
Last Modified: 2013-12-24
I'm a beginner with SQL and trying to develop a query that will return the last event logged on one of our databases against all of our clients.


Snippet of the table is below...
I'd like the first column to contain unique values, and the row returned should be the max of the event_ref column.
organisation_ref	event_date	create_user	event_ref
481570	23/12/2013	658840	61935080
481560	20/12/2013	554690	61931800
481560	20/12/2013	554690	61931840
481560	24/12/2013	7363780	61936480
481560	23/12/2013	7363780	61934200
481560	23/12/2013	7363780	61934740
481560	23/12/2013	7363780	61934330
481550	19/12/2013	7366260	61919410

Open in new window


Here is my SQL...
SELECT event.organisation_ref, event.event_date, event.create_user, MAX(event.event_ref) AS event_ref
FROM event
GROUP BY event.organisation_ref, event.event_date, event.create_user, event.event_ref
HAVING (((event.organisation_ref) Is Not Null))
ORDER BY event.organisation_ref DESC;

Where am I going wrong?
0
Comment
Question by:antonioking
  • 4
  • 2
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737693
you need event_ref with client mean Organization then try only this

SELECT event.organisation_ref, MAX(event.event_ref) AS event_ref
FROM event
GROUP BY event.organisation_ref, event.event_ref
HAVING (((event.organisation_ref) Is Not Null))
ORDER BY event.organisation_ref DESC;
0
 

Author Comment

by:antonioking
ID: 39737698
Thanks, but I want to include the event_date and create_user columns.

Regards
0
 
LVL 4

Expert Comment

by:rshq
ID: 39737699
Hi
 If you use Sql 2005 or higher please use this code

Select * from
(SELECT Row_number() over (partition by event.organisation_ref order by  event.event_date desc) as Row, event.organisation_ref,event.event_date,event.create_user, event.event_ref
FROM event where  event.organisation_ref Is Not Null) as a
where  (Row=1)

Open in new window

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:antonioking
ID: 39737703
I'm using Microsoft Access 2010, will this work in Access?
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 39737709
try this

SELECT event.organisation_ref, event.event_date, event.create_user, event.event_ref AS event_ref
FROM event
inner join (

SELECT organisation_ref, MAX(event_ref) AS event_ref
FROM event
GROUP BY organisation_ref, event_ref
HAVING (((organisation_ref) Is Not Null)) ) X
On X.organisation_ref = event.organisation_ref and  X.event_ref =  event.event_ref
0
 

Author Comment

by:antonioking
ID: 39737772
Hi,
Thanks for your assistance, the query seems to still return unwanted rows.

organisation_ref      event_date      create_user      event_ref
481570      23/12/2013      658840      61935080
481560      24/12/2013      7363780      61936480
481560      23/12/2013      7363780      61934740
481560      23/12/2013      7363780      61934330
481560      23/12/2013      7363780      61934200
481560      20/12/2013      554690      61931840
481560      20/12/2013      554690      61931800
481550      19/12/2013      7366260      61919410
0
 

Author Closing Comment

by:antonioking
ID: 39737799
had to slightly adapt this query but it worked for me.
Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how the fundamental information of how to create a table.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now