February 20, 2019 at 9:13 pm
Hi Team,
Can you please help me on this below query.
create table #test
(
companyid int,
companyname varchar(100),
actiondate datetime
)
insert into #test VALUES
(100369,'Bank of America Corporation','2019-02-05 03:04:49.000'),
(100369,'Bank of America Corporation','2019-01-31 08:13:22.000'),
(4633618,'Alphabet Inc.','2019-01-31 06:57:02.000'),
(4633618,'Alphabet Inc.','2019-01-31 06:55:57.000'),
(100369,'Bank of America Corporation','2018-12-31 03:41:05.000'),
(100369,'Bank of America Corporation','2018-12-31 03:41:05.000'),
(4633618,'Alphabet Inc.','2018-12-13 11:16:06.000'),
(4633618,'Alphabet Inc.','2018-12-13 11:16:05.000')
select * from #test
Present Output
companyid companyname actiondate
----------- ---------------------------------------------- -----------------------
100369 Bank of America Corporation 2019-02-05 03:04:49.000
100369 Bank of America Corporation 2019-01-31 08:13:22.000
4633618 Alphabet Inc. 2019-01-31 06:57:02.000
4633618 Alphabet Inc. 2019-01-31 06:55:57.000
100369 Bank of America Corporation 2018-12-31 03:41:05.000
100369 Bank of America Corporation 2018-12-31 03:41:05.000
4633618 Alphabet Inc. 2018-12-13 11:16:06.000
4633618 Alphabet Inc. 2018-12-13 11:16:05.000
Excepted output
companyid companyname actiondate
----------- ---------------------------------------------- -----------------------
100369 Bank of America Corporation 2019-02-05 03:04:49.000
100369 Bank of America Corporation 2019-01-31 08:13:22.000
100369 Bank of America Corporation 2018-12-31 03:41:05.000
100369 Bank of America Corporation 2018-12-31 03:41:05.000
4633618 Alphabet Inc. 2019-01-31 06:57:02.000
4633618 Alphabet Inc. 2019-01-31 06:55:57.000
4633618 Alphabet Inc. 2018-12-13 11:16:06.000
4633618 Alphabet Inc. 2018-12-13 11:16:05.000
Thanks
Bhanu
February 20, 2019 at 9:44 pm
You did not define the logic for the sorting, but I thing that this should do the trickwith cteMaxDate as (
select companyid, maxDate = max(actiondate)
from #test
group by companyid
)
select data.*
from #test as data
inner join cteMaxDate as cte on data.companyid = cte.companyid
order by cte.maxDate desc, data.companyname, data.actiondate desc
February 20, 2019 at 9:48 pm
Thank you so much it is working fine.
February 24, 2019 at 1:25 pm
Do you really need a timestamp accurate to three decimal places in the seconds? Why do you think that identifiers are numeric? What math do you do on your credit card number? Here's an attempt at correcting what you posted into a valid SQL table. It is still not properly normalized, but we will let that go for now.
CREATE TABLE Foobar
(company_id CHAR(6) NOT NULL,
company_name VARCHAR (100) NOT NULL,
action_date DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (company_id, action_date)
);
INSERT INTO Foobar
VALUES
('100369', 'Bank of America Corporation', '2019-02-05 03:04:49'),
('100369', 'Bank of America Corporation', '2019-01-31 08:13:22'),
('100369', 'Bank of America Corporation', '2018-12-31 03:41:05'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:57:02'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:55:57'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:06'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:05')
You don't seem to understand that a table has no sorted order. Again, this is a fundamental concept in RDBMS and SQL. Punch cards, on the other hand, can be sorted. There's no difference between your expected output and the input. Did you want to get the most recent action?
WITH X
AS
(SELECT company_id, company_name, action_date,
MAX(action_date) OVER (PARTITION BY company_id) AS last_action_date
FROM Foobar)
SELECT company_id, company_name, action_date
FROM
WHERE last_action_date = action_date
;
Please post DDL and follow ANSI/ISO standards when asking for help.
February 24, 2019 at 5:18 pm
kbhanu15 - Wednesday, February 20, 2019 9:13 PMYou're doing everything wrong. You don't seem to understand that by definition, a table must have a key, so you made it impossible, with all the columns being nullable, for this to ever be a table. In fact, you even had duplicate rows in the insertion statement! Your DDL should have prevented that, but what you posted is basically a deck of punch cards.Do you really need a timestamp accurate to three decimal places in the seconds? Why do you think that identifiers are numeric? What math do you do on your credit card number? Here's an attempt at correcting what you posted into a valid SQL table. It is still not properly normalized, but we will let that go for now.
CREATE TABLE Foobar
(company_id CHAR(6) NOT NULL,
company_name VARCHAR (100) NOT NULL,
action_date DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (company_id, action_date)
);INSERT INTO Foobar
VALUES
('100369', 'Bank of America Corporation', '2019-02-05 03:04:49'),
('100369', 'Bank of America Corporation', '2019-01-31 08:13:22'),
('100369', 'Bank of America Corporation', '2018-12-31 03:41:05'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:57:02'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:55:57'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:06'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:05')You don't seem to understand that a table has no sorted order. Again, this is a fundamental concept in RDBMS and SQL. Punch cards, on the other hand, can be sorted. There's no difference between your expected output and the input. Did you want to get the most recent action?
WITH X
AS
(SELECT company_id, company_name, action_date,
MAX(action_date) OVER (PARTITION BY company_id) AS last_action_date
FROM Foobar)
SELECT company_id, company_name, action_date
FROM
WHERE last_action_date = action_date
;
Reported as "Off topic or irrelevant".
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2019 at 10:49 am
jcelko212 32090 - Sunday, February 24, 2019 1:25 PMkbhanu15 - Wednesday, February 20, 2019 9:13 PMYou're doing everything wrong. You don't seem to understand that by definition, a table must have a key, so you made it impossible, with all the columns being nullable, for this to ever be a table. In fact, you even had duplicate rows in the insertion statement! Your DDL should have prevented that, but what you posted is basically a deck of punch cards.Do you really need a timestamp accurate to three decimal places in the seconds? Why do you think that identifiers are numeric? What math do you do on your credit card number? Here's an attempt at correcting what you posted into a valid SQL table. It is still not properly normalized, but we will let that go for now.
CREATE TABLE Foobar
(company_id CHAR(6) NOT NULL,
company_name VARCHAR (100) NOT NULL,
action_date DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (company_id, action_date)
);INSERT INTO Foobar
VALUES
('100369', 'Bank of America Corporation', '2019-02-05 03:04:49'),
('100369', 'Bank of America Corporation', '2019-01-31 08:13:22'),
('100369', 'Bank of America Corporation', '2018-12-31 03:41:05'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:57:02'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:55:57'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:06'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:05')You don't seem to understand that a table has no sorted order. Again, this is a fundamental concept in RDBMS and SQL. Punch cards, on the other hand, can be sorted. There's no difference between your expected output and the input. Did you want to get the most recent action?
WITH X
AS
(SELECT company_id, company_name, action_date,
MAX(action_date) OVER (PARTITION BY company_id) AS last_action_date
FROM Foobar)
SELECT company_id, company_name, action_date
FROM
WHERE last_action_date = action_date
;
Reported as "Off topic or irrelevant".
SSC, the SQL Server database site that despises relational theory LOL
February 25, 2019 at 11:13 am
Jeff Moden - Sunday, February 24, 2019 5:18 PMjcelko212 32090 - Sunday, February 24, 2019 1:25 PMkbhanu15 - Wednesday, February 20, 2019 9:13 PMYou're doing everything wrong. You don't seem to understand that by definition, a table must have a key, so you made it impossible, with all the columns being nullable, for this to ever be a table. In fact, you even had duplicate rows in the insertion statement! Your DDL should have prevented that, but what you posted is basically a deck of punch cards.Do you really need a timestamp accurate to three decimal places in the seconds? Why do you think that identifiers are numeric? What math do you do on your credit card number? Here's an attempt at correcting what you posted into a valid SQL table. It is still not properly normalized, but we will let that go for now.
CREATE TABLE Foobar
(company_id CHAR(6) NOT NULL,
company_name VARCHAR (100) NOT NULL,
action_date DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (company_id, action_date)
);INSERT INTO Foobar
VALUES
('100369', 'Bank of America Corporation', '2019-02-05 03:04:49'),
('100369', 'Bank of America Corporation', '2019-01-31 08:13:22'),
('100369', 'Bank of America Corporation', '2018-12-31 03:41:05'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:57:02'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:55:57'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:06'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:05')You don't seem to understand that a table has no sorted order. Again, this is a fundamental concept in RDBMS and SQL. Punch cards, on the other hand, can be sorted. There's no difference between your expected output and the input. Did you want to get the most recent action?
WITH X
AS
(SELECT company_id, company_name, action_date,
MAX(action_date) OVER (PARTITION BY company_id) AS last_action_date
FROM Foobar)
SELECT company_id, company_name, action_date
FROM
WHERE last_action_date = action_date
;Reported as "Off topic or irrelevant".
SSC, the SQL Server database site that despises relational theory LOL
Nah, just supercilious pedantry. If you're going to define the clustering key, at least define it properly by its usage, as shown by the OP:
( company_id, action_date DESC ) /*to avoid having to do an actual sort to produce the list shown*/
Finally, I don't see how defining a "char(6)" column to hold at least a 7-char id matches "relational theory", or even common sense. And, if you're going to be pedantic enough to use char(n) at all, then you have to add the CHECK statement(s) to verify that data coming in is actually numeric.
Btw, tables can be sorted, just as punch cards could. Celko's bizarre obsession with punch cards rages on, for no apparent reason at all.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 25, 2019 at 11:22 am
patrickmcginnis59 10839 - Monday, February 25, 2019 10:49 AMJeff Moden - Sunday, February 24, 2019 5:18 PMjcelko212 32090 - Sunday, February 24, 2019 1:25 PMkbhanu15 - Wednesday, February 20, 2019 9:13 PMYou're doing everything wrong. You don't seem to understand that by definition, a table must have a key, so you made it impossible, with all the columns being nullable, for this to ever be a table. In fact, you even had duplicate rows in the insertion statement! Your DDL should have prevented that, but what you posted is basically a deck of punch cards.Do you really need a timestamp accurate to three decimal places in the seconds? Why do you think that identifiers are numeric? What math do you do on your credit card number? Here's an attempt at correcting what you posted into a valid SQL table. It is still not properly normalized, but we will let that go for now.
CREATE TABLE Foobar
(company_id CHAR(6) NOT NULL,
company_name VARCHAR (100) NOT NULL,
action_date DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (company_id, action_date)
);INSERT INTO Foobar
VALUES
('100369', 'Bank of America Corporation', '2019-02-05 03:04:49'),
('100369', 'Bank of America Corporation', '2019-01-31 08:13:22'),
('100369', 'Bank of America Corporation', '2018-12-31 03:41:05'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:57:02'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:55:57'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:06'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:05')You don't seem to understand that a table has no sorted order. Again, this is a fundamental concept in RDBMS and SQL. Punch cards, on the other hand, can be sorted. There's no difference between your expected output and the input. Did you want to get the most recent action?
WITH X
AS
(SELECT company_id, company_name, action_date,
MAX(action_date) OVER (PARTITION BY company_id) AS last_action_date
FROM Foobar)
SELECT company_id, company_name, action_date
FROM
WHERE last_action_date = action_date
;Reported as "Off topic or irrelevant".
SSC, the SQL Server database site that despises relational theory LOL
Nah, just supercilious pedantry. If you're going to define the clustering key, at least define it properly by its usage, as shown by the OP:
( company_id, action_date DESC ) /*to avoid having to do an actual sort to produce the list shown*/
Finally, I don't see how defining a "char(6)" column to hold at least a 7-char id matches "relational theory", or even common sense. And, if you're going to be pedantic enough to use char(n) at all, then you have to add the CHECK statement(s) to verify that data coming in is actually numeric.
Btw, tables can be sorted, just as punch cards could. Celko's bizarre obsession with punch cards rages on, for no apparent reason at all.
Yeah I sort of get dumped on when I argue (no matter how politely) what I think should be correct, so I'm pretty sympathetic to Celko. I admire his stubbornness, and after all if anybody should understand the goals of SQL, it should be guys like him. Don't get me wrong, I have made it a premise that objectively incorrect stuff is going to be accepted by SSC'ers and I will have no influence on that, but its a bit therapeutic for me to at least point and laugh 🙂
February 25, 2019 at 11:32 am
ScottPletcher - Monday, February 25, 2019 11:13 AMpatrickmcginnis59 10839 - Monday, February 25, 2019 10:49 AMJeff Moden - Sunday, February 24, 2019 5:18 PMjcelko212 32090 - Sunday, February 24, 2019 1:25 PMkbhanu15 - Wednesday, February 20, 2019 9:13 PMYou're doing everything wrong. You don't seem to understand that by definition, a table must have a key, so you made it impossible, with all the columns being nullable, for this to ever be a table. In fact, you even had duplicate rows in the insertion statement! Your DDL should have prevented that, but what you posted is basically a deck of punch cards.Do you really need a timestamp accurate to three decimal places in the seconds? Why do you think that identifiers are numeric? What math do you do on your credit card number? Here's an attempt at correcting what you posted into a valid SQL table. It is still not properly normalized, but we will let that go for now.
CREATE TABLE Foobar
(company_id CHAR(6) NOT NULL,
company_name VARCHAR (100) NOT NULL,
action_date DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (company_id, action_date)
);INSERT INTO Foobar
VALUES
('100369', 'Bank of America Corporation', '2019-02-05 03:04:49'),
('100369', 'Bank of America Corporation', '2019-01-31 08:13:22'),
('100369', 'Bank of America Corporation', '2018-12-31 03:41:05'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:57:02'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:55:57'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:06'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:05')You don't seem to understand that a table has no sorted order. Again, this is a fundamental concept in RDBMS and SQL. Punch cards, on the other hand, can be sorted. There's no difference between your expected output and the input. Did you want to get the most recent action?
WITH X
AS
(SELECT company_id, company_name, action_date,
MAX(action_date) OVER (PARTITION BY company_id) AS last_action_date
FROM Foobar)
SELECT company_id, company_name, action_date
FROM
WHERE last_action_date = action_date
;Reported as "Off topic or irrelevant".
SSC, the SQL Server database site that despises relational theory LOL
Nah, just supercilious pedantry. If you're going to define the clustering key, at least define it properly by its usage, as shown by the OP:
( company_id, action_date DESC ) /*to avoid having to do an actual sort to produce the list shown*/Finally, I don't see how defining a "char(6)" column to hold at least a 7-char id matches "relational theory", or even common sense. And, if you're going to be pedantic enough to use char(n) at all, then you have to add the CHECK statement(s) to verify that data coming in is actually numeric.
Btw, tables can be sorted, just as punch cards could. Celko's bizarre obsession with punch cards rages on, for no apparent reason at all.
Yeah I sort of get dumped on when I argue (no matter how politely) what I think should be correct, so I'm pretty sympathetic to Celko. I admire his stubbornness, and after all if anybody should understand the goals of SQL, it should be guys like him. Don't get me wrong, I have made it a premise that objectively incorrect stuff is going to be accepted by SSC'ers and I will have no influence on that, but its a bit therapeutic for me to at least point and laugh 🙂
Don't worry, we're laughing right back. Over-priced theory-bound-only consultants were always the bane of getting to something that actually worked. No rdbms existence follows all of Codd's original rules, so, we'd have no dbs at all if we didn't just move ahead with what can actually work.
The performance on your systems must be awful, what with the unnecessary CHECK constraints and ridiculously over-long char columns instead of simple ints. You can't stay in logical design mode forever, as Celko and you seem to want to do. You have to turn that logical model into a physical design, with all the compromises that entails.
Yes, I've railed on this site too about the vast number of people who simply refuse to do any type of logical design at all and insist that it's not needed. By the same token, you can't rail against every physical design simply because it doesn't match your idea of a perfect logical design. To actually use the data design at all, you have to go from logical to physical.
In the imaginary world, everything has some magical "universal" key that you can somehow divine and pretend it works, like a DUNS (hint: they're are not "universal" either!), In reality, there is no "universal" value, nor, even it if existed, would one have time to find it. How would one even go about determining how the majority of companies in, say, Egypt, Ukraine, South America and Peru assign company identifying values?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 25, 2019 at 11:44 am
patrickmcginnis59 10839 - Monday, February 25, 2019 11:22 AMScottPletcher - Monday, February 25, 2019 11:13 AMpatrickmcginnis59 10839 - Monday, February 25, 2019 10:49 AMJeff Moden - Sunday, February 24, 2019 5:18 PMjcelko212 32090 - Sunday, February 24, 2019 1:25 PMkbhanu15 - Wednesday, February 20, 2019 9:13 PMYou're doing everything wrong. You don't seem to understand that by definition, a table must have a key, so you made it impossible, with all the columns being nullable, for this to ever be a table. In fact, you even had duplicate rows in the insertion statement! Your DDL should have prevented that, but what you posted is basically a deck of punch cards.Do you really need a timestamp accurate to three decimal places in the seconds? Why do you think that identifiers are numeric? What math do you do on your credit card number? Here's an attempt at correcting what you posted into a valid SQL table. It is still not properly normalized, but we will let that go for now.
CREATE TABLE Foobar
(company_id CHAR(6) NOT NULL,
company_name VARCHAR (100) NOT NULL,
action_date DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (company_id, action_date)
);INSERT INTO Foobar
VALUES
('100369', 'Bank of America Corporation', '2019-02-05 03:04:49'),
('100369', 'Bank of America Corporation', '2019-01-31 08:13:22'),
('100369', 'Bank of America Corporation', '2018-12-31 03:41:05'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:57:02'),
('4633618', 'Alphabet Inc.', '2019-01-31 06:55:57'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:06'),
('4633618', 'Alphabet Inc.', '2018-12-13 11:16:05')You don't seem to understand that a table has no sorted order. Again, this is a fundamental concept in RDBMS and SQL. Punch cards, on the other hand, can be sorted. There's no difference between your expected output and the input. Did you want to get the most recent action?
WITH X
AS
(SELECT company_id, company_name, action_date,
MAX(action_date) OVER (PARTITION BY company_id) AS last_action_date
FROM Foobar)
SELECT company_id, company_name, action_date
FROM
WHERE last_action_date = action_date
;Reported as "Off topic or irrelevant".
SSC, the SQL Server database site that despises relational theory LOL
Nah, just supercilious pedantry. If you're going to define the clustering key, at least define it properly by its usage, as shown by the OP:
( company_id, action_date DESC ) /*to avoid having to do an actual sort to produce the list shown*/Finally, I don't see how defining a "char(6)" column to hold at least a 7-char id matches "relational theory", or even common sense. And, if you're going to be pedantic enough to use char(n) at all, then you have to add the CHECK statement(s) to verify that data coming in is actually numeric.
Btw, tables can be sorted, just as punch cards could. Celko's bizarre obsession with punch cards rages on, for no apparent reason at all.
Yeah I sort of get dumped on when I argue (no matter how politely) what I think should be correct, so I'm pretty sympathetic to Celko. I admire his stubbornness, and after all if anybody should understand the goals of SQL, it should be guys like him. Don't get me wrong, I have made it a premise that objectively incorrect stuff is going to be accepted by SSC'ers and I will have no influence on that, but its a bit therapeutic for me to at least point and laugh 🙂
Don't worry, we're laughing right back. Over-priced theory-bound-only consultants were always the bane of getting to something that actually worked. No rdbms existence follows all of Codd's original rules, so, we'd have no dbs at all if we didn't just move ahead with what can actually work.
The performance on your systems must be awful, what with the unnecessary CHECK constraints and ridiculously over-long char columns instead of simple ints. You can't stay in logical design mode forever, as Celko and you seem to want to do. You have to turn that logical model into a physical design, with all the compromises that entails.
Yes, I've railed on this site too about the vast number of people who simply refuse to do any type of logical design at all and insist that it's not needed. By the same token, you can't rail against every physical design simply because it doesn't match your idea of a perfect logical design. To actually use the data design at all, you have to go from logical to physical.
In the imaginary world, everything has some magical "universal" key that you can somehow divine and pretend it works, like a DUNS (hint: they're are not "universal" either!), In reality, there is no "universal" value, nor, even it if existed, would one have time to find it. How would one even go about determining how the majority of companies in, say, Egypt, Ukraine, South America and Peru assign company identifying values?
LOL yeah when I started out in IT, programmers were MANIC about correctness. Design was a HUGE thing, and this is obviously a very subjective thing, but the thought pattern was that it was important to think and talk about these things as there were real reasons for doing so. Obviously that's all fake news nowadays, I get it %^)
February 25, 2019 at 12:07 pm
The performance on your systems must be awful, what with the unnecessary CHECK constraints and ridiculously over-long char columns instead of simple ints.
Incidently, do character fields really slow down SQL Server that much? My only experience with this are balanced trees that are agnostic about the keys. I also found scanning character strings and classifying the individual characters into "classes" to be ridiculously fast. Is this just part of the drawbacks of SQL Server? Heck even doing the '[0-9][0-9]...' thing just doesn't seem to impact things in a query, does it fall apart as a constraint tho? Could it be that constraints in general are harmful to SQL Server performance? IE., sure they have a cost, but is that cost unreasonable? If that's the case, are constraints bad entirely because of the bad performance?
Don't get me wrong, Theres not a doubt in my mind that abandoning RDBMS principles will benefit performance, but was this really the primary driver behind RDBMS in the first place?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply