Want one values with similar ids

Question

Want one values with similar ids

somillohani

SSC Rookie

Points: 35
More actions
February 11, 2013 at 3:41 am

#394155

Hi friends
i have a question
Id Transcation amount
1 20000
1 30000
2 40000
2 20000
2 20000
3 45000
3 30000
3 5000
I want result as
1 20000
2 40000
3 5000
pls help me....

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

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

Rather than just give us data and results, explain the problem. What should the query be doing to return the results? Are you saying you want one value? The "first" value? Note there is no "first" or "last" in SQL Server without an order by in the query.

Also, please provide DDL (table create statements and insert statements for data).

Moved to T-SQL forums.

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

I was going to take a shot in the dark because I thought I knew what you wanted but for ID 1 you have the value that was listed first in your sample, which is also the lowest value. For ID 2 you also have the "first" but this time it is the highest value. However for ID 3 you have the "last" value and it is also the lowest value. So the business rules here are impossible to decipher based on your post.

Please take a few minutes and read the article found by following the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 3

a coded example of what Seans' first thought: clearly item 2 you wanted, at 40,000, does not give the desired results.

you'll need to clarify either the biz logic or the error int eh desired output.

/*

--Results

idasTR

120000

220000

330000

*/

With MyCTE (Id,Transcation_amount)

AS

(

SELECT '1','20000' UNION ALL

SELECT '1','30000' UNION ALL

SELECT '2','40000' UNION ALL

SELECT '2','20000' UNION ALL

SELECT '3','45000' UNION ALL

SELECT '3','30000' UNION ALL

SELECT '3','5000'

)

select id,min(Transcation_amount) asTR

FROM MYCTE

GROUP BY ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

Another shot in the dark. Maybe the OP wants the minimum amount from unique transactions for each ID?

;WITH Trans (ID, TransactionAmt) AS (

SELECT '1',20000 UNION ALL

SELECT '1',30000 UNION ALL

SELECT '2',40000 UNION ALL

SELECT '2',20000 UNION ALL

SELECT '3',45000 UNION ALL

SELECT '3',30000 UNION ALL

SELECT '3',5000)

SELECT ID, Amt=MIN(Amt)

FROM (

SELECT ID, Amt=MIN(TransactionAmt)

FROM Trans

GROUP BY ID, TransactionAmt

HAVING COUNT(TransactionAmt) = 1

) a

GROUP BY ID

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

somillohani SSC Rookie Points: 35 More actions · Answer 5

HI Steve Jones - SSC Editor

sorry for my weird question.

But actually i don't want value greater or smaller using min or max inbuilt method i want RANDOM value which is correspond to similar id...

My DDL is

Create table images(id int,images varchar(90))

insert into images values('1','someimagepath')

insert into images values('2','someimagepath')

insert into images values('3','someimagepath')

and i want result AS:

ID IMAGE

1 someimagepath

2 someimagepath

3 someimagepath

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Something like this then: