SQL select to return the latest row for each vacancyID

I'm working with a table which holds versions of vacancies...

It has fields:

vacID (char16, which is a FK)
vacDetails (string)
dateCreated (date)

and others which are not relevant

For each vacID, there will be 1 or many rows, each with a seperate dateCreated.

However, I only want to return the latest row (ie. semantically the current version of the vacancy), for each vacancy.

I *can* edit the table to include a ROWID (a pk) field if needs be, but would like to see both options if possible - with ROWID and without ROWID.

how do I do this?
many thanks
zorba111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can try this

SELECT * FROM 
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY vacDetails ORDER BY VACID,dateCreated  DESC) RN FROM TABLE
) TBL WHERE RN = 1

Open in new window

0
johnsoneSenior Oracle DBACommented:
Not sure why you would partition by the details, which would be a string.  You would want to partition by the id.
SELECT * 
FROM   (SELECT *, 
               Row_number() 
                 OVER( 
                   partition BY vacid 
                   ORDER BY datecreated DESC) RN 
        FROM   table) TBL 
WHERE  rn = 1 

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you only want to know the ID and the last DateCreated then should be a simple query:
SELECT vacID, MAX(dateCreated)
FROM TableName
GROUP BY vacID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

YZlatCommented:
 SELECT t1.VacID, t1.vacDetails, t1.dateCreated
FROM Table1 as t1 INNER JOIN (SELECT VacID, MAX(dateCreated) as MaxDateCreated FROM Table1 GROUP BY VacID) as a
ON t1.VacID=a.VacID and t1.dateCreated=a.MaxDateCreated

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks like you have working answers, Vitor's above if you don't need vacDetails, and johnsone if you do, but fwiw here's an article SQL Server GROUP BY Solutions that illustrates both in points 1 and 5.
0
zorba111Author Commented:
Sorry guys, I need to tell you I'm using Sybase's "Advantage Database Server" v9.0.

When I tried to run the queries by Vikas and Johnsone, I got errors.

Unfortunately the support pages online are down right now, but I did see that Advantage supports SQL-92, and there aren't any mention of keywords "OVER" and "PARTITION" in SQL-92.

I had tried to edit the question with sample data and a sample output, but EE wouldn't let me edit the question again... dang... so I lost all the typing... anyway, here it is again...

table xVacAdvert, looks a bit like this (simplified):

vacID      vacDetails                 dateCreated
101         "c# developer v1"    29/08/2015
101         "c# developer v2"    30/08/2015
103         "sql admin v1"          01/08/2015
103         "sql admin v2"          02/08/2015
103         "sql admin v3"          03/08/2015
110         "security engineer"  10/08/2015

and the output will be like this (simplified, there will be other fields like vacDetails):

vacID      vacDetails               dateCreated
101         "c# developer v2"  30/08/2015
103         "sql admin v2"        03/08/2015
110         "security engineer" 10/08/2015

thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is an ANSI script so should work in any RDBMS:
SELECT T1.vacID, T1.vacDetails, T1.dateCreated
FROM TableName T1
WHERE T1.dateCreated = (SELECT MAX(T2.dateCreated)
                    FROM TableName T2
                    WHERE T2.vacID=T1.vacID)

Open in new window

0
zorba111Author Commented:
@Jim: I made a second edit to the inital question to include the information in my 2nd post above *within five minutes of the original posting* but EE wouldn't let me make the changes.

Anyway, that was just failsafe: I didn't expect to get non-ANSI answers in a question that was tagged with "Query Syntax" as the most important tag, and then *several* wellknown RDBMS products.

In any case, apologies to anyone who gave non-ANSI answers.

(FYI Advantage has been in the Sybase family only for a short time, so I don't expect people in the Sybase community to know it well yet.)
0
johnsoneSenior Oracle DBACommented:
Unfortunately, windowing is allowed in most mainstream systems.  Oracle, SQL Server and a few others that I have worked with allow that.  Never worked with Sybase.

The solutions posted above by YZlat or Vitor Montalvão should work just fine as ANSI solutions.  It has to hit the table twice, which is why the windowing is preferred.
0
zorba111Author Commented:
@Vitor, thanks for your answers:

in #2, what is T2?

I'm only dealing with one table (xVacAdverts).
Are you suggesting I create a temp table?
thanks
0
johnsoneSenior Oracle DBACommented:
T2 is the table alias for the table name in the sub query.  Since you are using the same table twice, you need aliases to distinguish between the 2.

Putting your real table name into the query, it would look like this:
SELECT T1.vacid, 
       T1.vacdetails, 
       T1.datecreated 
FROM   xvacadverts T1 
WHERE  T1.datecreated = (SELECT Max(T2.datecreated) 
                         FROM   xvacadverts T2 
                         WHERE  T2.vacid = T1.vacid) 

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, is what johnsone explained above. This thread is going too fast for me :)
0
zorba111Author Commented:
Hi Johnsone, your last post works just fine thanks!
Thanks too to Viktor for your contribution along the the way.

Now, I'm gonna spend a few minutes trying to break it down and understand it.

BTW Advantage is a very streamlined database, so please don't judge the other Sybase RDBMS products by Advantage's shortcomings ;-)
0
zorba111Author Commented:
I can see that "dateCreated" needs to be a datetime!
(as >1 version of a job may be created on the same day, but *not* in the same millisecond lol)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.