T SQL Select with order by and join

Posted on 2009-07-01
Last Modified: 2012-05-07
I am trying to write a t-sql query where i am trying to select top 1 column order by desc and making a join with another table. Here's the sample of my desired output along with the table structure:

ID      Comments      MyID            Date
1      A                  11            11:34PM
2      B                  22            12:00PM
3      C                  33            01:20AM
1      AA                  11            05:00PM
1      AAA            11            03:00AM

ID      Name
1      X
2      Y
3      Z

ID      Comments      MyID              Name
1      AAA            11            X
2      B                  22            Y
3      C                  33            Z

I want a join from Table1 and Table2 with Table1 returning just top 1 row order by ID desc and join with Table2.
Question by:PuneetKSaxena
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

Expert Comment

ID: 24752956
u ll need a column to uniquely identify each row of table 1
Assuming u r using MS SQL:
Alter Table Table1
add SrNo int identity

select b.*
from (select id,max(SrNo) as SrNo
from table1
group by id) a
inner join table1 b
        on a.SrNo=B.SrNo
inner join table2 C

In Oracle:
u hv to add a sequence in place of identity column

Expert Comment

ID: 24752974
Alternatively u can use following query if u r sure that date column is unique and stores time till millisecond.

select b.*
from (select id,max([Date]) as MyDate
from table1
group by id) a
inner join table1 b
        on a.MyDate=B.MyDate
inner join table2 C
LVL 31

Expert Comment

ID: 24753129
can you try this one?

from table1 as a join table2 as b
on and
      select dt from
      (select MAX( as dt, from TableA as ta where  group by id) as t
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 32

Expert Comment

ID: 24755192
I'm not sure I understand your question. You indicate "order by ID desc" but your desired output shows the ID field in ascending order. Also, what is the criteria being used to determine top 1? Is it the earliest time (date) or the longest comments?

Accepted Solution

IncisiveOne earned 125 total points
ID: 24761704
I am with awking00 ... there is not enough specific info in the question to produce the posted result.  The SQL is easy, once you can specify what you want.
LVL 32

Expert Comment

ID: 25439713
I've been on two weeks vacation and didn't get the opportunity to see this, but I'm curious why a comment was selected as an accepted solution, especially when the comment included an agreement with my comment?

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

734 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