current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I have Process SQL Server table and some columns and their values.

              ID                      Step StateID  SenderNo                
61cc2608-81b8-48a2-89ce-8887438985bb    9   125     1750069133      
61cc2608-81b8-48a2-89ce-8887438985bb    8   120     1111111162  
61cc2608-81b8-48a2-89ce-8887438985bb    5   116     1111111162      
61cc2608-81b8-48a2-89ce-8887438985bb    2   115     3900383669      
61cc2608-81b8-48a2-89ce-8887438985bb    1   113     1750069133      
14dsfgd4-123d-21ds-86ds-124sgslkgj31    9   125     1750069133  
14dsfgd4-123d-21ds-86ds-124sgslkgj31    8   120     1111111162  
14dsfgd4-123d-21ds-86ds-124sgslkgj31    5   116     1111111162  
14dsfgd4-123d-21ds-86ds-124sgslkgj31    2   115     3900383669  
14dsfgd4-123d-21ds-86ds-124sgslkgj31    1   113     1750069133  
21456qwf-674s-75df-53sg-125sdfsgsd47    5   116     1111111162  
21456qwf-674s-75df-53sg-125sdfsgsd47    2   115     3900383669  
21456qwf-674s-75df-53sg-125sdfsgsd47    1   113     1750069133

I want to get data according to highest Step value. Like that;

 ID                                  Step StateID   SenderNo                
61cc2608-81b8-48a2-89ce-8887438985bb    9   125     1750069133
14dsfgd4-123d-21ds-86ds-124sgslkgj31    9   125     1750069133
21456qwf-674s-75df-53sg-125sdfsgsd47    5   116     1111111162

I have no idea how to write the necessary T-SQL query in ASP.Net (C#)...

share|improve this question

closed as off-topic by T I, Shoban, Abbas, Soner Gönül, jadarnel27 Nov 19 '13 at 15:47

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist" – T I, Shoban, Abbas, Soner Gönül, jadarnel27
If this question can be reworded to fit the rules in the help center, please edit the question.
    
How are those the highest Step? I see rows with 8... can you clarify? Or do you mean the highest step per ID? –  Marc Gravell♦ Nov 19 '13 at 13:36
    
You can use ADO.NET component or LinqToSql. Try to see this technet guide: social.technet.microsoft.com/wiki/contents/articles/… –  Stefano Bafaro Nov 19 '13 at 13:36
3  
Use this post, learncsharp.org/…, and execute that code server-side in the appropriate event. There are many other ways of getting at the data, and I prefer Dapper, but this is a start. –  Michael Perrenoud Nov 19 '13 at 13:37
    
The highest step for a given ID? –  Fred Nov 19 '13 at 13:38
    
@MarcGravell yes, it means highest step of per ID –  Bip Nov 19 '13 at 13:39
show 2 more comments

2 Answers 2

up vote 4 down vote accepted

The first thing to do is to write the TSQL that you need. By my reckoning, that is:

select ID, Step, StateID, SenderNo
from (
    select *, row_number() over(partition by ID order by Step desc) as [_row]
    from Process) x where x.[_row]=1

Next, you need to talk to the server in code. I'm going to use "dapper" for convenience:

using(var conn = new SqlConnection(ConnectionString))
{
    var rows = conn.Query<Process>(@"
select ID, Step, StateID, SenderNo
from (
    select *, row_number() over(partition by ID order by Step desc) as [_row]
    from Process) x where x.[_row]=1").ToList();

    // ... use rows
}

where Process is a class you define with suitable ID, Step, StateID, SenderNo properties. Or you can avoid that via dynamic:

using(var conn = new SqlConnection(ConnectionString))
{
    var rows = conn.Query(@"
select ID, Step, StateID, SenderNo
from (
    select *, row_number() over(partition by ID order by Step desc) as [_row]
    from Process) x where x.[_row]=1").ToList();

    foreach(var row in rows)
    {
        Console.WriteLine(row.ID); // dynamic member resolution
        Console.WriteLine(row.Step);
        //...             
    }
}
share|improve this answer
add comment
up vote 2 down vote

There are lots of examples on the web showing how to query a database in ASP.NET but the query you want would be along the lines of

SELECT *
FROM (
    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY id ORDER BY step DESC) 
    FROM tbl
) t
WHERE rn=1
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.