How Do I Select Data From Table With A Particular Priority

Question

How Do I Select Data From Table With A Particular Priority

sushantkatte

SSChasing Mays

Points: 647
More actions
August 1, 2015 at 12:14 pm

#320294

DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
INSERT INTO @Table VALUES
(873939, 'Reverse', '7/24/2015 3:31:18'),
(873939, 'Tamper', '7/24/2015 3:30:00'),
(873939, 'Meter', '7/24/2015 3:31:22'),
(873940, 'Reverse', '7/24/2015 3:30:00'),
(873940, 'Tamper', '7/24/2015 3:31:22')
i want to select the data priority wise
the o/p should look like
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'

Viewing 10 posts - 1 through 9 (of 9 total)

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

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

sushantkatte (8/1/2015)
i want to select the data priority wise
the o/p should look like
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'

What is the criteria for prioritizing the data? After sorting on minv_code, how should each group, or window, be sorted? Based on your desired output, it is not datetime nor is it alpha. Not enough info.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

I am not sure I follow your logic on the output and am concerned that you may you have oversimplified your problem in your post.

based on what you have provided...here is one way to provide what you have requested

SELECT

minv_code

, alert_msg

, alert_time

FROM @table

ORDER BY minv_code,

CASE WHEN alert_msg ='Meter' THEN 1

WHEN alert_msg = 'Tamper' THEN 2

WHEN alert_msg = 'Reverse' THEN 3

ELSE 0

END

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

LinksUp SSCertifiable Points: 6526 More actions · Answer 3

If the ordering is indeed the alter_msg, then I would create another table to define the sorting method. Hard coding each possibility could quickly become a nightmare! :w00t:

Here is a solution with an additional table thrown in:

DECLARE @Table TABLE

(minv_code INT,

alert_msg varchar(10),

alert_time Datetime)

DECLARE @SortOrder TABLE

(

Sort_id int,

alert_msg varchar(10)

)

INSERT INTO @Table VALUES

(873939, 'Reverse', '7/24/2015 3:31:18'),

(873939, 'Tamper', '7/24/2015 3:30:00'),

(873939, 'Meter', '7/24/2015 3:31:22'),

(873940, 'Reverse', '7/24/2015 3:30:00'),

(873940, 'Tamper', '7/24/2015 3:31:22')

INSERT INTO @SortOrder VALUES

(1, 'Meter'),

(2, 'Tamper'),

(3, 'Reverse')

select *

from @Table t

inner join @SortOrder s on s.alert_msg = t.alert_msg

order by t.minv_code, s.sort_id

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

sushantkatte SSChasing Mays Points: 647 More actions · Answer 4

Thank You very much @SScrazy for providing the solution. i am very new to the sql server programming and have started learning recently. could you please help me understand the logic of script that you provided and how it works?

Actually this question was asked to me in an interview

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

sushantkatte (8/1/2015)
Thank You very much @SScrazy for providing the solution. i am very new to the sql server programming and have started learning recently. could you please help me understand the logic of script that you provided and how it works?
Actually this question was asked to me in an interview

did you get the job?

anyways...I am assuming that you understand 'ORDER BY' and that its the 'CASE' expression that you are unfamiliar with.

I suggest that you read the following....this will explain in far better detail than I can.

https://technet.microsoft.com/en-us/library/ms181765(v=sql.105).aspx

also...as @LinksUp suggested, you can consider other solutions.....maybe a new table to store sort orders or add an additional column to an exg table.

it all depends.....without more detail on your actual tables it cannot be readily answered

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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

LinksUp (8/1/2015)
If the ordering is indeed the alter_msg, then I would create another table to define the sorting method. Hard coding each possibility could quickly become a nightmare! :w00t:
Here is a solution with an additional table thrown in:
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
DECLARE @SortOrder TABLE
(
Sort_id int,
alert_msg varchar(10)
)
INSERT INTO @Table VALUES
(873939, 'Reverse', '7/24/2015 3:31:18'),
(873939, 'Tamper', '7/24/2015 3:30:00'),
(873939, 'Meter', '7/24/2015 3:31:22'),
(873940, 'Reverse', '7/24/2015 3:30:00'),
(873940, 'Tamper', '7/24/2015 3:31:22')
INSERT INTO @SortOrder VALUES
(1, 'Meter'),
(2, 'Tamper'),
(3, 'Reverse')
select *
from @Table t
inner join @SortOrder s on s.alert_msg = t.alert_msg
order by t.minv_code, s.sort_id

+1000 to that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

sushantkatte (8/1/2015)
Thank You very much @SScrazy for providing the solution. i am very new to the sql server programming and have started learning recently. could you please help me understand the logic of script that you provided and how it works?
Actually this question was asked to me in an interview

Now that you have answers, I'm curious... how is it that you're applying for a job that requires knowledge of T-SQL while you have virtually none? Does your resume say that you've worked with T-SQL to any extent? Did you tell the interviewer that you were just getting started in the world of SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sushantkatte SSChasing Mays Points: 647 More actions · Answer 8

I was not able to understand how the Case statement is working,particularly the THEN part (THEN 1,THEN 2,THEN 2)

CASE WHEN alert_msg ='Meter' THEN 1

WHEN alert_msg = 'Tamper' THEN 2

WHEN alert_msg = 'Reverse' THEN 2

I am a newbie in SQL but i have basic knowledge and understanding. i was applying for entry level and internship positions where i would get to learn in detail.

and as far as getting the job goes. i haven't heard back from them yet:-P

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 9

sushantkatte (8/2/2015)
I was not able to understand how the Case statement is working,particularly the THEN part (THEN 1,THEN 2,THEN 2)
CASE WHEN alert_msg ='Meter' THEN 1
WHEN alert_msg = 'Tamper' THEN 2
WHEN alert_msg = 'Reverse' THEN 2
I am a newbie in SQL but i have basic knowledge and understanding. i was applying for entry level and internship positions where i would get to learn in detail.
and as far as getting the job goes. i haven't heard back from them yet:-P

The code actually contained the following CASE statement.

CASE WHEN alert_msg ='Meter' THEN 1

WHEN alert_msg = 'Tamper' THEN 2

WHEN alert_msg = 'Reverse' THEN 3

END

CASE is conditional code that operates as a single operand. In this case, it says that if the alert_msg column contains 'Meter', then set the result of the CASE to 1. It does similar with 2 and 3. Since the CASE statement is in the ORDER BY, the resulting 1, 2, or 3 for each row is used to sort the data.

Good luck on your internship. I guess I don't understand why they would ask such an intermediate question to someone applying for and entry level position.

Shifting gears, a bit... if you intend to make a career in the world of SQL Server or front-end programming with good knowledge of SQL Server programming, I strongly recommend you buy a copy of the SQL Server Developer Edition (usually < $60USD) for yourself. Think of it as a text-book on steroids. It's the "Enterprise Edition" with special licensing for learners/developers and works on your desktop/laptop so that you don't have to have a Windows Server setup. Also lookup and then download and install "Books Online", which is the "help" system for SQL Server. That information is sometimes easy to find via Google but you may like the way the information is organized in Books Online better. It also contains some tutorial information.

As a study guide for a beginner, you might try the following link with the understanding that they use ANSI SQL there. SQL Server uses parts of the ANSI Standard but there's a whole lot of extra stuff in SQL Server that work really well.

http://www.w3schools.com/sql/default.asp

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)