Solved

Query to return unique row based on column 1

Posted on 2013-12-24
7
438 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how the fundamental information of how to create a table.

730 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