Menu
I am a sql server newbie and trying to select all the customers which have more than 1 orderid. The table looks as follows:
CREATE TABLE [dbo].[orders](
[customerid] [int] NULL,
[orderid] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (1, 2)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (1, 3)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (2, 4)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (2, 5)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (3, 1)
662
Syntax: SELECT * FROM table_name ORDER BY column_name; For Multiple column order, add the name of the column by which you'd like to sort records first. The column that is entered at first place will get sorted first and likewise.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
SQL ORDER BY Multiple Columns However we can use multiple columns in ORDER BY clause. When multiple columns are used in ORDER BY, first the rows will be sorted based on the first column and then by the second column.
Order by 3 DESC. In this query, column birthdate is at the 3rd position; therefore, we can use three in the Order by clause to sort results on this column data. Note: I would not recommend using column position in Order By clause. You should always use a column name in Order by clause.
select customerid
, count(*) as order_count
from orders
group by
customerid
having count(*) > 1
as you'll probably need customer data at some point, you could also try:
select *
from customers
where exists (
select count(*)
from orders
where customers.id = customerid
group by customerid
having count(*) > 1
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
