Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order top 1

Author  Topic 

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 03:19:20
Hi all,
How do i select first top 1 id from given tblA.. so that multiple records for
1.MM06707003-3Waiting finance
2.MM06707003-4complete
3.MM13707001-4complete
will return only first one distinct adjref record that is
1.MM06707003-3Waiting finance
3.MM13707001-4complete
should i order distinct top 1? 

-- Prepare sample data
DECLARE	@tblA TABLE (Ref varchar(12),Client varchar(12), Submit char(1), CN char(1), GM char(1), FN char(1))

INSERT	@tblA
SELECT	'MM06707003', 'SURETEX','Y','Y','Y','N' UNION ALL
SELECT	'MM06707003', 'SURETEX','Y','N','N','N' UNION ALL
SELECT	'MM13707001', 'SURETEX','Y','N','N','N'

--my workaround
SELECT distinct   Ref, Client,  Submit, CN, GM, FN,
CASE 
WHEN  Submit='Y' AND CN='Y' THEN 
	CASE 
		WHEN GM='Y' AND FN='Y' THEN '4-Complete'
		WHEN GM='Y' AND FN='N' THEN '3-Waiting Finance'
		WHEN GM='N' THEN '2-Waiting GM'
		END 
WHEN Submit='Y' AND CN='N' THEN 
	'4-Complete'
WHEN Submit='N' THEN
	'1-Incomplete'
END AS Status	
FROM @tblA
order by status

--expected results
SELECT	'MM06707003', 'SURETEX','Y','Y','Y','N', '3-Waiting Finance'  UNION ALL
SELECT	'MM13707001', 'SURETEX','Y','N','N','N', 'Complete'



~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-17 : 03:50:27
Replace DISTINCT with TOP 1 and add an ORDER BY 7 at the end.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 04:13:58
i still need different refid status... i just need to eliminate the extra refid status..

~~~Focus on problem, not solution ¯\(º_o)/?~~~
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-07-17 : 04:45:51
[code]-- Prepare sample data
DECLARE @tblA TABLE (Ref varchar(12),Client varchar(12), Submit CHAR(1), CN CHAR(1), GM CHAR(1), FN CHAR(1))

INSERT @tblA
SELECT 'MM06707003', 'SURETEX','Y','Y','Y','N' UNION ALL
SELECT 'MM06707003', 'SURETEX','Y','N','N','N' UNION ALL
SELECT 'MM13707001', 'SURETEX','Y','N','N','N'

--my workaround

DECLARE @stage TABLE (Ref varchar(12),Client varchar(12), Submit CHAR(1), CN CHAR(1), GM CHAR(1), FN CHAR(1), Status varchar(20))

INSERT INTO @stage (Ref, Client, Submit, CN, GM, FN, Status)
SELECT Ref, Client, Submit, CN, GM, FN,
CASE
WHEN Submit='Y' AND CN='Y' THEN
CASE
WHEN GM='Y' AND FN='Y' THEN '4-Complete'
WHEN GM='Y' AND FN='N' THEN '3-Waiting Finance'
WHEN GM='N' THEN '2-Waiting GM'
END
WHEN Submit='Y' AND CN='N' THEN
'4-Complete'
WHEN Submit='N' THEN
'1-Incomplete'
END AS Status
FROM @tblA

SELECT *
FROM @stage s
WHERE Status = (SELECT TOP 1 Status FROM @stage x WHERE x.Ref = s.Ref ORDER BY Status DESC)

--expected results
SELECT 'MM06707003', 'SURETEX','Y','Y','Y','N', '3-Waiting Finance' UNION ALL
SELECT 'MM13707001', 'SURETEX','Y','N','N','N', '4-Complete'[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 04:55:48
Exactly khtan! :)
just i want to order status ascending :P

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 05:00:06
but any workaround without using any temp table is highly appriciated

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-07-17 : 05:05:34
quote:
Originally posted by maya_zakry

but any workaround without using any temp table is highly appriciated

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

If you are using SQL 2000 then upgrade to 2005, you will have more option

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 07:48:33
ic.. so what 2005 could do in this situation :P

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-17 : 07:56:30
[code]SELECT Ref,
Client,
Submit,
CN,
GM,
FN,
Status
FROM (
SELECT Ref,
Client,
Submit,
CN,
GM,
FN,
Status,
ROW_NUMBER() OVER (PARTITION BY Ref ORDER BY Status) AS RecID
FROM (
SELECT Ref,
Client,
Submit,
CN,
GM,
FN,
CASE
WHEN Submit = 'Y' AND CN = 'Y' THEN
CASE
WHEN GM = 'Y' AND FN = 'Y' THEN '4-Complete'
WHEN GM = 'Y' AND FN = 'N' THEN '3-Waiting Finance'
WHEN GM = 'N' THEN '2-Waiting GM'
END
WHEN Submit = 'Y' AND CN = 'N' THEN '4-Complete'
WHEN Submit='N' THEN '1-Incomplete'
END AS Status
FROM @tblA
) AS d
) AS e
WHERE RecID = 1[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-17 : 08:09:11
Try this. It might work on both SQL Server 2000 and SQL Server 2005
SELECT		a.Ref,
		a.Client,
		a.Submit,
		a.CN,
		a.GM,
		a.FN,
		CASE
			WHEN Submit = 'Y' AND CN = 'Y' THEN 
				CASE 
					WHEN GM = 'Y' AND FN = 'Y' THEN '4-Complete'
					WHEN GM = 'Y' AND FN = 'N' THEN '3-Waiting Finance'
					WHEN GM = 'N' THEN '2-Waiting GM'
				END 
			WHEN Submit = 'Y' AND CN = 'N' THEN '4-Complete'
			WHEN Submit = 'N' THEN '1-Incomplete'
		END AS Status	
FROM		@tblA AS a
WHERE		(SELECT COUNT(*) FROM @tblA AS b WHERE b.Ref = a.Ref AND b.CN >= a.CN AND b.GM >= a.GM AND b.FN >= a.FN) = 1
Another Maya Zakry delivery


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 20:51:10
so it's free delivery rite peter!

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 21:05:50
so may i ask pliz.. what this command do exactly? it works.. but im not sure how.. how this query eliminate the unwanted record just like what i need? the object b always have to be more than object a?
WHERE (SELECT COUNT(*) FROM @tblA AS b WHERE
b.Ref = a.Ref AND
b.CN >= a.CN AND
b.GM >= a.GM AND
b.FN >= a.FN) = 1
thanks

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-17 : 21:12:36
one more thing, why ididnt get any records when im applying this to my real table, meaning, replacing the sample table(@tblA) to table : ItemAdjManual???

in case if u forget, i need to sort by the "status", not the submit,cn,gm,finance

so this will do (back to KHTAN>> :P)


DECLARE	@tblA TABLE (AdjRef varchar(12),ClientID varchar(12),CustomerName varchar(50), Submit CHAR(1), CN CHAR(1), GM CHAR(1), FN CHAR(1), Status varchar(50))

INSERT INTO @tblA (AdjRef, ClientID, CustomerName, Submit, CN, GM, FN, Status)

SELECT DISTINCT	AdjRef, ClientID, CustomerName, Submit, CN, GM, FN,
	CASE 
	WHEN  Submit='Y' AND CN='Y' THEN 
		CASE 
			WHEN GM='Y' AND FN='Y' THEN '4-Complete / Approved'
			WHEN GM='Y' AND FN='N' THEN '3-Waiting Finance Approval'
			WHEN GM='N' THEN '2-Waiting GM Approval'
		END 
	WHEN Submit='Y' AND CN='N' THEN '4-Complete / Approved'
	WHEN Submit='N' THEN '1-Incomplete'

	END AS Status	
	FROM ItemAdjManual a 
	LEFT JOIN CustomerMaster c ON c.CustomerID=a.ClientID			
	WHERE a.AdjRef LIKE '%' + @AdjRef + '%' AND
		 a.ClientID LIKE '%' + @ClientID + '%' AND
		 a.ItemID LIKE '%' + @ItemID + '%'

SELECT	DISTINCT AdjRef, ClientID, CustomerName AS ClientName, SubString(Status, 3, 50) AS STatus--, Submit, CN, GM, FN
FROM	@tblA s
WHERE	Status	= (SELECT TOP 1 Status FROM @tblA x WHERE x.AdjRef = s.AdjRef ORDER BY Status )


~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-19 : 01:44:52
If you look very closely to the CASE thingy (the Status), my suggestion does exactly the same thing!
Your CASE code is the same thing as sorting by

ORDER BY SUBMIT DESC, CN DESC, GM DESC, FN DESC (according to original post)
My suggestion below produces the exact output as you wanted in your original post.
Is there sometihng new that you haven't told us?
-- Prepare sample data
DECLARE	@tblA TABLE (Ref varchar(12),Client varchar(12), Submit char(1), CN char(1), GM char(1), FN char(1))

INSERT	@tblA
SELECT	'MM06707003', 'SURETEX','Y','Y','Y','N' UNION ALL
SELECT	'MM06707003', 'SURETEX','Y','N','N','N' UNION ALL
SELECT	'MM13707001', 'SURETEX','Y','N','N','N'

-- Show the result
SELECT		a.Ref,
		a.Client,
		a.Submit,
		a.CN,
		a.GM,
		a.FN,
		CASE
			WHEN Submit = 'Y' AND CN = 'Y' THEN 
				CASE 
					WHEN GM = 'Y' AND FN = 'Y' THEN '4-Complete'
					WHEN GM = 'Y' AND FN = 'N' THEN '3-Waiting Finance'
					WHEN GM = 'N' THEN '2-Waiting GM'
				END 
			WHEN Submit = 'Y' AND CN = 'N' THEN '4-Complete'
			WHEN Submit = 'N' THEN '1-Incomplete'
		END AS Status	
FROM		@tblA AS a
WHERE		(SELECT COUNT(*) FROM @tblA AS b WHERE b.Ref = a.Ref AND b.CN >= a.CN AND b.GM >= a.GM AND b.FN >= a.FN) = 1
If you now want to change the order of "status", just replace >= with <= !!!!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-07-19 : 02:11:13
umm... try this sample

SELECT	'MM06707003', 'SURETEX','Y','Y','Y','N' UNION ALL
SELECT	'MM06707003', 'SURETEX','Y','N','N','N' UNION ALL
SELECT	'MM06707003', 'SURETEX','N','N','N','N' UNION ALL  --new added
SELECT	'MM13707001', 'SURETEX','Y','N','N','N'

it's suppose to print :-
MM06707003 SURETEX N N N N 1-Incomplete
MM13707001 SURETEX Y N N N 4-Complete
Not :-
MM06707003 SURETEX Y Y Y N 3-Waiting Finance
MM13707001 SURETEX Y N N N 4-Complete



~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-19 : 05:50:30
I forgot the Submit part
-- Prepare sample data
DECLARE	@tblA TABLE (Ref varchar(12),Client varchar(12), Submit char(1), CN char(1), GM char(1), FN char(1))

INSERT	@tblA
SELECT	'MM06707003', 'SURETEX','Y','Y','Y','N' UNION ALL
SELECT	'MM06707003', 'SURETEX','Y','N','N','N' UNION ALL
SELECT	'MM06707003', 'SURETEX','N','N','N','N' UNION ALL  --new added
SELECT	'MM13707001', 'SURETEX','Y','N','N','N'

-- Do the work
SELECT		a.Ref,
		a.Client,
		a.Submit,
		a.CN,
		a.GM,
		a.FN,
		CASE
			WHEN Submit = 'Y' AND CN = 'Y' THEN 
				CASE 
					WHEN GM = 'Y' AND FN = 'Y' THEN '4-Complete'
					WHEN GM = 'Y' AND FN = 'N' THEN '3-Waiting Finance'
					WHEN GM = 'N' THEN '2-Waiting GM'
				END 
			WHEN Submit = 'Y' AND CN = 'N' THEN '4-Complete'
			WHEN Submit = 'N' THEN '1-Incomplete'
		END AS Status
FROM		@tblA AS a
WHERE		(SELECT COUNT(*) FROM @tblA AS b WHERE b.Ref = a.Ref AND b.Submit <= a.Submit AND b.CN <= a.CN AND b.GM <= a.GM AND b.FN <= a.FN) = 1
ORDER BY	a.Ref


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site