如何获得特定的行 [英] how to get particular row at first
问题描述
如何以相同的顺序获得第一个剩余记录的特定行
ex:
table
abc
------
2种子10
3 kadd 12
1质量32
4 ssds 55
---------
i希望第一行是b = mass
how to get particular row at first remaining records in same order
ex:
table
a b c
------
2 seed 10
3 kadd 12
1 mass 32
4 ssds 55
---------
i want first row is b=mass
推荐答案
已经发布了另一个已发布的解决方案,但我认为可能需要替代方案。
乍一看,您似乎只想按[a] 列的顺序排列表格你需要哪种情况(向@hypermellow点头)
Posting this solution as another has already been posted but I think an alternative might be required.
At first sight it looks as if you just want the table to be ordered by column[a]
in which case you need (nod to @hypermellow)
select a, b, c from [table] order by a
将生成
1 mass 32
2 seed 10
3 kadd 12
4 ssds 55
然而你的措辞
如何以相同的顺序获得第一个剩余记录的特定行
how to get particular row at first remaining records in same order
表示您可能需要类似
suggests that you might want something like
with CTE AS (
select a, b, c, 1 as rownum from [table] where b = 'mass'
UNION ALL
select a, b, c, 1 + ROW_NUMBER() OVER (ORDER BY b)
FROM [table] where b <> 'mass'
)
SELECT a, b, c FROM CTE order by rownum
将产生结果
1 mass 32
3 kadd 12
2 seed 10
4 ssds 55
这里的兴趣点是我手动将 rownum
1分配给我想要的记录,然后允许sql生成行号我基于所需的任何订单(我使用列 [b]
只是为了突出显示差异) - 请注意我添加 1
生成的行号,以确保子查询不会以rownum = 1结束与我的第一个查询冲突。
然后我只是UNION这两个查询。
如果您只想要返回一条记录(在任何一种情况下),那么按照建议使用 TOP 1
Amaan23(但你的措辞意味着这不是你想要的)
The point of interest here is that I have manually assigned a rownum
of 1 to the record I want first, then allowed sql to generate row numbers for me based on whichever order is required (I used column [b]
just to highlight the difference) - note that I add 1
to the generated row number to ensure the sub-query doesn't end up with a rownum=1 to clash with my first query.
I then just UNION the two queries.
If you did only want a single record returned (in either case) then use TOP 1
as suggested by Amaan23 (but your wording implies that this is not what you want)
CHill60答案的一个更简单的替代方法是使用CASE
语句在ORDER BY
子句中:
A simpler alternative to CHill60's answer is to use aCASE
statement in theORDER BY
clause:
SELECT
a,
b,
c
FROM
[table]
ORDER BY
CASE
WHEN b = 'mass' THEN 0
ELSE 1
END,
b
;
这篇关于如何获得特定的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!