Do I need a group by to get last value ??

Question

Post reply

Do I need a group by to get last value ??

jbalbo

SSCertifiable

Points: 7583
More actions
June 24, 2013 at 12:35 pm

#395025

Hi
I will butcher this question...
I am selecting data where I assign a value to a field where the first character is a value for its position and the second the value
example a is first and b is second, so field is 1A OR 2B call it BOX
some records have more than one value on the same day but I want the one if the first position
so I order by name, date, BOX desc
I want just the last value for the name and the date
I am try to put this in a matrix SSRS report
so my end results would look sort of like
. Date Date ....
name A A
name2 B A .. etc
Thanks
Joe

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

care to share some sample data and expected results for that data?

may clarify your issue.

best wishes

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Not difficult but we need something more to work with. Please post DDL and sample data to get the help needed. For more information on how to do it, read the article linked in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Pretty spare on the details here. I will take a shot in the dark. Maybe you can use ROW_NUMBER and group by date?

If you want more detail help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

sorry.. I SPAZZED out!! I'll take a few and get it together better....

jbalbo SSCertifiable Points: 7583 More actions · Answer 5

Here is snippet of the code

SELECT Client.LName + ', ' + ISNULL(Client.FName, ' ') AS clientname, RECORDED_SERVICE.STARTTIME,

CASE WHEN dbo.DELIVERY_METHOD.Code = '001' AND CAD227 = 'on' AND

dbo.PLACE_OF_SERVICE.FullName = 'Clubhouse & Community' THEN '5B' WHEN dbo.DELIVERY_METHOD.Code = '001' AND CAD227 = 'on' AND

dbo.PLACE_OF_SERVICE.FullName = 'Community' THEN '4O' WHEN dbo.DELIVERY_METHOD.Code = '001' AND CAD227 = 'on' AND

dbo.PLACE_OF_SERVICE.FullName = 'CLubhouse' THEN '3S' WHEN dbo.DELIVERY_METHOD.Code = '002' AND (CAD204 = 'on' OR

CAD205 = 'on' OR

CAD207 = 'on') THEN '2T' WHEN CAD226 = 'on' AND DateDiff(day, dbo.AdmitDate, STARTTIMe) < 31 THEN '1A' ELSE '6 ' END AS BOX, DAY(RECORDED_SERVICE.STARTTIME) AS DayofMonth

FROM tables...

WHERE ........

ORDER BY Client.LName, RECORDED_SERVICE.STARTTIME, box DESC

The output looks like

clientnameSTARTTIMEBOXExpr2

Frr, Chelsea2013-06-05 17:09:00.0006 5

Frr, Chelsea2013-06-06 10:17:00.0006 6

Frr, Chelsea2013-06-18 12:27:00.0001A18

Frr, Chelsea2013-06-19 11:48:00.0006 19

Frr, Chelsea2013-06-19 11:48:00.0001A19

sorry..

So in this example I only want the record for Expr2 = 19 with box = 1A(last record for the 19th)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

jbalbo (6/24/2013)
The output looks like
clientnameSTARTTIMEBOXExpr2
Frr, Chelsea2013-06-05 17:09:00.0006 5
Frr, Chelsea2013-06-06 10:17:00.0006 6
Frr, Chelsea2013-06-18 12:27:00.0001A18
Frr, Chelsea2013-06-19 11:48:00.0006 19
Frr, Chelsea2013-06-19 11:48:00.0001A19
sorry..
So in this example I only want the record for Expr2 = 19 with box = 1A(last record for the 19th)

ok...so no set up scripts or data.

if I read this correctly...and based on what you have asked.....to get the "last record" in the sample above you will need to create an order by client/starttime/expr2 and finally by "box"...is this correct?

looking at your code and the case statements that create "box"...what are you expecting to return in search order for each of the possibilities?

eg:

CREATE TABLE [dbo].[box]

(

[box] [varchar](2) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[box] ([box]) VALUES (N'5B')

INSERT [dbo].[box] ([box]) VALUES (N'40')

INSERT [dbo].[box] ([box]) VALUES (N'35')

INSERT [dbo].[box] ([box]) VALUES (N'2T')

INSERT [dbo].[box] ([box]) VALUES (N'1A')

INSERT [dbo].[box] ([box]) VALUES (N'6')

--==WHAT IS THE SELECTION ORDER FOR THE FOLLOWING

SELECT * FROM box