Complex SQL grouping only 1 field

1

Hi there
I'm somewhat new to complex SQL and I'm trying to use a grouping on SIN and want to sort on edit_Time, but need to have Sin LAST PROGRAM PAY_AMT in the select statement for the report

EDIT_Time YR MNTH SIN LAST PROGRAM Pay

2020-09-21 10:56:30.713 2023 01-JUL ***011079 BEAUDIN BCECTB 99.00
2020-09-21 10:17:29.690 2051 01-JUL ***011079 BEAUDIN BCECTB 104.01
2020-09-21 10:04:57.733 2051 01-JUL ***011079 BEAUDIN BCECTB 104.00
2020-09-18 21:48:51.963 2051 01-JUL ***918469 DAN BCECTB
2020-09-18 15:44:40.560 2051 01-JUL ***011079 BEAUDIN BCECTB 66.00
2020-09-18 15:08:28.763 2023 01-JUL ***011079 BEAUDIN BCECTB 55.00
2020-09-04 09:59:23.953 2020 04-OCT ***847715 RICHARD BCECTB
2020-09-04 09:59:12.073 2020 04-OCT ***847715 RICHARD BCECTB
2020-08-19 14:59:44.037 2020 01-JUL ***183435 SAWDEN BCECTB 1.00
2020-08-19 14:52:06.523 2020 01-JUL ***183435 SAWDEN BCECTB 2.05
2020-08-19 12:58:53.113 2020 01-JUL ***847715 RICHARD BCECTB
2020-08-19 08:55:43.073 2020 01-JUL ***183435 SAWDEN BCECTB 99.00
2020-08-19 08:38:33.067 2020 01-JUL ***183435 SAWDEN BCECTB 99.00
2020-08-18 15:35:45.220 2020 01-JUL ***847715 RICHARD BCECTB
2020-08-18 13:24:30.933 2020 01-JUL ***847715 RICHARD BCECTB
2020-08-18 13:10:28.563 2020 01-JUL ***847715 RICHARD BCECTB

I have
Select EDIT_Time ,YR, MNTH, SIN , LAST, PROGRAM, Pay
from EDITED_PAYMENT_View
group by SIN, EDIT_Time ,YR, MNTH, LAST, PROGRAM, Pay
order by [EDIT_Time] desc

but that doesn't properly group the SIN's and sort EDIT_Time

this groups and sorts properly: but I need the rest of the fields in the select
SELECT TOP (100) PERCENT SIN, EDIT_Time
FROM EDITED_PAYMENT_View
GROUP BY SIN, EDIT_Time

Any help would be great
Thx

2

From the sample data you provided, which rows would you want to get back in the result. I'm not 100% clear on what you want to do.

3

Thx for the reply
I'm looking to list it similar to this:

2020-09-21 10:56:30.713 2023 01-JUL ***011079 BEAUDIN BCECTB 99.00
2020-09-21 10:17:29.690 2051 01-JUL ***011079 BEAUDIN BCECTB 104.01
2020-09-21 10:04:57.733 2051 01-JUL ***011079 BEAUDIN BCECTB 104.00
2020-09-18 15:44:40.560 2051 01-JUL ***011079 BEAUDIN BCECTB 66.00
2020-09-18 15:08:28.763 2023 01-JUL ***011079 BEAUDIN BCECTB 55.00

2020-09-18 21:48:51.963 2051 01-JUL ***918469 DAN BCECTB

2020-09-04 09:59:23.953 2020 04-OCT ***847715 RICHARD BCECTB
2020-09-04 09:59:12.073 2020 04-OCT ***847715 RICHARD BCECTB
2020-08-19 12:58:53.113 2020 01-JUL ***847715 RICHARD BCECTB
2020-08-18 15:35:45.220 2020 01-JUL ***847715 RICHARD BCECTB
2020-08-18 13:24:30.933 2020 01-JUL ***847715 RICHARD BCECTB
2020-08-18 13:10:28.563 2020 01-JUL ***847715 RICHARD BCECTB

2020-08-19 14:59:44.037 2020 01-JUL ***183435 SAWDEN BCECTB 1.00
2020-08-19 14:52:06.523 2020 01-JUL ***183435 SAWDEN BCECTB 2.05
2020-08-19 08:55:43.073 2020 01-JUL ***183435 SAWDEN BCECTB 99.00
2020-08-19 08:38:33.067 2020 01-JUL ***183435 SAWDEN BCECTB 99.00

All the last names are grouped together and the first column: EDIT_Time is sorted

4

You don't need a GROUP BY for that, just an ORDER BY, if I understand correctly:

SELECT *
FROM EDITED_PAYMENT_View
ORDER BY LAST, EDIT_Time
1 Like
5

Thanks for the reply
Seems the DBA has cleaned out the Dev data for the next round of tests so I have to wait to test the "order by" sql again