T-SQL SubQuery Help Please

Question

Post reply

T-SQL SubQuery Help Please

gs1975

Ten Centuries

Points: 1057
More actions
March 16, 2021 at 12:41 pm

#3858497

Hi,
I know this will be rather obvious to many, but I am struggling writing a sub-query at the moment.
I am trying to return just the 1 row for each BadgeNo attendee based on the latest date updated joining it with a conf_code in table B. (Illustrative data below)
Table A Table B
ID Badge N0 Date_Updated Badge No Conf_Code
1 1 2021-03-12 00:00:00 1 ABC
2 1 2021-03-15 00:00:00 1 DEF
3 2 2021-03-11 00:00:00 2 ABC
4 2 2021-03-14 00:00:00 2 EFG
5 3 2021-03-14 00:00:00 3 GHI

I would like the following data set to be returned.
Badge N0 Date_Updated Conf_Code
1 2021-03-15 00:00:00 DEF
2 2021-03-14 00:00:00 EFG
3 2021-03-14 00:00:00 GHI

Thanks in advance for any help that can be provided.
George

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

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Well how are you deciding which conf code for each badge number to use?

This reply was modified 3 years, 8 months ago by ZZartin.

ktflash Default port Points: 1400 More actions · Answer 2

To elaborate your question from ZZ, how do you decide the combination of Badge And Conf_Code.

Getting each the latest Date for each Badge is not the issue, but how do you decide which Conf_Code to add.

Until we know what ur trying to do heres the SQL Code for those who want to continue testing once OP posts additional info.

create table #A 
(ID nvarchar(1)
,BadgeN  nvarchar(1)
,date_updated datetime)


create table #b
(BadgeN  nvarchar(1)
,Conf_Code nvarchar(3))

insert into #A
values 
('1','1','2021-12-03 00:00:00')
,('3','2','2021-11-03 00:00:00')
,('5','3','2021-14-03 00:00:00')
,('2','1','2021-15-03 00:00:00')
,('4','2','2021-14-03 00:00:00')


insert into #B
values 
('1','ABC')
,('1','DEF')
,('2','ABC')
,('2','EFG')
,('3','GHI')

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

ScottPletcher SSC Guru Points: 101095 More actions · Answer 3

SQL Server does not automatically determine a row sequence, so you'll need a column in Table B that provides an order, such as an date_inserted or a $IDENTITY column. For now, I'll assume a date_inserted column.

SELECT A.[Badge No], A.Date_Updated, B.Conf_Code 
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [Badge No] ORDER BY Date_Updated) AS row_num
    FROM [Table A]
) AS A
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [Badge No] ORDER BY Date_Inserted) AS row_num
    FROM [Table B]
) AS B ON B.[Badge No] = A.[Badge No] AND B.row_num = 1
WHERE A.row_num = 1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Set up the data:

if object_id('tempdb..#a','U') is not null
    drop table #a

if object_id('tempdb..#b','U') is not null
    drop table #b
go

create table #a 
(
    ID nvarchar(1),
    BadgeN  nvarchar(1),
    date_updated datetime
)

create table #b
(BadgeN  nvarchar(1)
,Conf_Code nvarchar(3))

insert into #a
values 
('1','1','2021-03-12 00:00:00')
,('3','2','2021-03-11 00:00:00')
,('5','3','2021-03-14 00:00:00')
,('2','1','2021-03-15 00:00:00')
,('4','2','2021-03-14 00:00:00')


insert into #b
values 
('1','ABC')
,('1','DEF')
,('2','ABC')
,('2','EFG')
,('3','GHI')

Select query with cross apply

;with cte as
(
    select distinct BadgeN
      from #A
)
select a.BadgeN, a.date_updated, b.Conf_Code
  from cte
 cross apply (select top(1) *
                from #a a 
               where a.BadgeN = cte.badgeN
               order by a.date_updated desc) a
 cross apply (select top(1) *
                from #b b 
               where b.BadgeN = cte.badgeN
               order by b.Conf_Code desc) b;

or maybe even this would do:

select a.BadgeN, MAX(a.date_updated) date_updated, MAX(b.Conf_Code) Conf_Code
  from #a a
 inner join #b b
         on b.BadgeN = a.BadgeN
 group by a.BadgeN;

gs1975 Ten Centuries Points: 1057 More actions · Answer 5

Hi all,

Thanks for the replies so far. I am pretty new to the data as I have recently started in the role.

I think the code is derived from elsewhere, but I will go and find that out soon.

I will try a few things based on Scott's code and any new information I get.

I will let you know if I have any further information, or if I am able to return the rows I want.

Thanks again,

George

gs1975 Ten Centuries Points: 1057 More actions · Answer 6

Hi everyone,

I just wanted to let you know that a developer has now resolved this who has returned from leave. He knew the data better than me and used the principal's of Scott's script to write this.

Thanks for everyone's help.

Thanks,

George