如何从此结果集中选择前1个值

I have this query in a stored procedure:

我在存储过程中有这个查询:

SELECT    DISTINCT
 a.UserProfileId,
 a.FromProfileId,
  mm.FromProfileId as ToProfileID
FROM      ( SELECT
          mup.UserProfileId,
          mmb.FromProfileId
        FROM      dbo.Mindcracker_MailBox mmb
        LEFT JOIN Mindcracker_UserProfile mup
          ON mmb.ToProfileId=mup.UserProfileId
        WHERE     mup.UserProfileId=1144 ) a
LEFT JOIN  dbo.Mindcracker_MailBox mm
ON a.FromProfileId=mm.ToProfileId

The above query produces the result below:

以上查询产生以下结果:

UserProfielId  FromProfileID  ToProfielID
1144    1152    1144
1144    1152    1378
1144    1152    1483
1144    1333    1143
1144    1333    1148
1144    1333    1290
1144    1333    1297
1144    1333    1333
1144    1333    1378
1144    1378    1143
1144    1378    1185
1144    1378    1333
1144    1378    1378
1144    1483    1143
1144    1483    1144
1144    1483    1297
1144    1483    1483
1144    11526   1148
1144    11526   1290
1144    11526   1333
1144    11526   1378
1144    11526   11526

I want to get only the top value on the basis FromProfileId Column and my output should be this:

我想在FromProfileId Column的基础上得到唯一的值,我的输出应该是这样的:

1144    1152    1144
1144    1333    1143
1144    1378    1143
1144    1483    1143

6 个解决方案

#1

try this query to get top 4 value as you OutPut.

尝试此查询以获得OutPut的前4个值。

Execute this Query After you will receive the result from stored procedure:

执行此查询从存储过程收到结果后:

SELECT TOP 4 FromProfielId , UserProfielId, ToProfielID FROM [Your TableName];

SELECT TOP 4 FromProfielId,UserProfielId,ToProfielID FROM [Your TableName];

I hope you will get your solution.

我希望你能得到你的解决方案。

#2

I think you should remove distinct and add group by UserProfileId, FromProfileId

我认为您应该通过UserProfileId,FromProfileId删除distinct并添加组

#3

Try adding this

尝试添加此功能

ORDER BY amount [ColumnName] LIMIT 4

#4

execute below query on the result set you got from stored procedure.

对从存储过程获得的结果集执行以下查询。

SELECT TOP 1 * FROM [TableName] ORDER BY FromProfielId;

#5

try using ranking_functions tutorial

尝试使用ranking_functions教程

 with cte as (
        SELECT    DISTINCT
         a.UserProfileId,
         a.FromProfileId,
          mm.FromProfileId as ToProfileID
        FROM      ( SELECT
                  mup.UserProfileId,dense_rank() over (order by mmb.FromProfileid) as rn

                FROM      dbo.Mindcracker_MailBox mmb
                LEFT JOIN Mindcracker_UserProfile mup
                  ON mmb.ToProfileId=mup.UserProfileId
                WHERE     mup.UserProfileId=1144 ) a
        LEFT JOIN  dbo.Mindcracker_MailBox mm
        ON a.FromProfileId=mm.ToProfileId
        order by profile_ID
        )

        select * from cte where rn between 1 AND 4

#6

Although this question is for a long time ago, but probably someone can find it useful.

虽然这个问题很久以前,但可能有人会觉得它很有用。

To get the first row of each data set, you need to window your result set using window functions.

要获取每个数据集的第一行,您需要使用窗口函数来窗口化结果集。

I just make your result set and query the output based on that. It can be expanded to the original query.

我只是制作你的结果集并根据它查询输出。它可以扩展到原始查询。

Create table

CREATE TABLE #tbl(UserProfielId INT, FromProfileID INT, ToProfielID INT)

Sample data

INSERT #tbl
SELECT 1144  ,  1152  ,  1144 UNION ALL
SELECT 1144  ,  1152  ,  1378 UNION ALL
SELECT 1144  ,  1152  ,  1483 UNION ALL
SELECT 1144  ,  1333  ,  1143 UNION ALL
SELECT 1144  ,  1333  ,  1148 UNION ALL
SELECT 1144  ,  1333  ,  1290 UNION ALL
SELECT 1144  ,  1333  ,  1297 UNION ALL
SELECT 1144  ,  1333  ,  1333 UNION ALL
SELECT 1144  ,  1333  ,  1378 UNION ALL
SELECT 1144  ,  1378  ,  1143 UNION ALL
SELECT 1144  ,  1378  ,  1185 UNION ALL
SELECT 1144  ,  1378  ,  1333 UNION ALL
SELECT 1144  ,  1378  ,  1378 UNION ALL
SELECT 1144  ,  1483  ,  1143 UNION ALL
SELECT 1144  ,  1483  ,  1144 UNION ALL
SELECT 1144  ,  1483  ,  1297 UNION ALL
SELECT 1144  ,  1483  ,  1483 UNION ALL
SELECT 1144  ,  11526 ,  1148 UNION ALL
SELECT 1144  ,  11526 ,  1290 UNION ALL
SELECT 1144  ,  11526 ,  1333 UNION ALL
SELECT 1144  ,  11526 ,  1378 UNION ALL
SELECT 1144  ,  11526 ,  11526

Query

Use ROW_NUMBER function to window your result set in a CTE and then make a query based on your main query.

使用ROW_NUMBER函数在CTE中窗口显示结果集,然后根据主查询进行查询。

;WITH C AS(
    SELECT  ROW_NUMBER() OVER (PARTITION BY FromProfileID ORDER BY FromProfileID) AS Rn
            ,UserProfielId
            ,FromProfileID
            ,ToProfielID
    FROM #tbl
)

SELECT  UserProfielId
        ,FromProfileID
        ,ToProfielID
FROM C
WHERE Rn = 1

Output

UserProfielId   FromProfileID   ToProfielID
1144            1152            1144
1144            1333            1143
1144            1378            1143
1144            1483            1143
1144            11526           1148

SQLFiddle Demo

#1