Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

SQL: select max group by

Hi

Looking to select the last data row in a table for a group of customers

this gives me their last iInquiryId
SELECT     MAX(iInquiryId) AS inq
FROM         dbo.tbInquiry
WHERE     (iCustomerId IN (1,2,3,4))
GROUP BY iCustomerId

I also want vComment, dtInquiry but adding these in only gives me the last row of them all
adding a group by  iCustomerId, vComment, dtInquiry returns too much data

any ideas
0
louise_8
Asked:
louise_8
1 Solution
 
James ElliottManaging DirectorCommented:
Something like this, which I haven't tested:

SELECT
a.* 
FROM
(
		SELECT iInquiryID, vComment, dtInquiry, ROW_NUMBER() OVER (PARTITION BY iCustomerID ORDER BY iInquiryID DESC) as rn
		WHERE iCustomerid IN (1,2,3,4)
) a
WHERE a.rn = 1

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try something like this:
SELECT  iInquiryId AS inq, vComment, dtInquiry  
 FROM         dbo.tbInquiry
 WHERE   iInquiryId = (SELECT MAX(iInquiryId) WHERE iCustomerId IN (1,2,3,4))

Open in new window

0
 
James ElliottManaging DirectorCommented:
Sorry, missing a line.

SELECT
a.* 
FROM
(
		SELECT iInquiryID, vComment, dtInquiry, ROW_NUMBER() OVER (PARTITION BY iCustomerID ORDER BY iInquiryID DESC) as rn
		FROM dbo.tbInquiry
		WHERE iCustomerid IN (1,2,3,4)
) a
WHERE a.rn = 1

Open in new window

0
Technology Partners: 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!

 
Pratima PharandeCommented:
Select A.*
From dbo.tbInquiry A
inner join
(
SELECT  iCustomerId ,   MAX(iInquiryId) AS inq
FROM         dbo.tbInquiry
WHERE     (iCustomerId IN (1,2,3,4))
GROUP BY iCustomerId ) As X
on A.iCustomerId =X.iCustomerId  and A.iInquiryId = X.inq
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please read up this article I wrote for this kind of questions:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
it explains all you need in regards to the needs vs syntax
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Forgot the FROM in the subselect:
SELECT  iInquiryId AS inq, vComment, dtInquiry  
 FROM  dbo.tbInquiry
 WHERE   iInquiryId = (SELECT MAX(iInquiryId)  FROM  dbo.tbInquiry WHERE iCustomerId IN (1,2,3,4))

Open in new window

0
 
awking00Commented:
select inq, iCustomerId, vComment, dtInquiry from
(select iInquiryId as inq, iCustomerid, vComment, dtInquiry,
 row_number() over (partition by iCustomerId order by iInquiryId desc) rn
 from tbo.tblInquiry
 where iCustomerId in (1,2,3,4)) as x
where x.rn = 1;
0
 
louise_8Author Commented:
thanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now