August 3, 2015 at 7:23 am
I rand code below but got an error said
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
select distinct ORDERID from ORDER where ORDERID = '123456' order by ORDERDATE desc // error
I have to modify code to
select distinct ORDERID,ORDERDate from ORDER where ORDERID = '123456' order by ORDERDATE desc
But, I really need only "ORDERID".
How to make "ORDERDATE" not displaying?
August 3, 2015 at 7:36 am
adonetok (8/3/2015)
I rand code below but got an error saidORDER BY items must appear in the select list if SELECT DISTINCT is specified
select distinct ORDERID from ORDER where ORDERID = '123456' order by ORDERDATE desc // error
I have to modify code to
select distinct ORDERID,ORDERDate from ORDER where ORDERID = '123456' order by ORDERDATE desc
But, I really need only "ORDERID".
How to make "ORDERDATE" not displaying?
Why do you need the distinct in the first place? How many orders have the same OrderID???
_______________________________________________________________
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/
August 3, 2015 at 7:43 am
I used code below, it works but I am not sure it is good.
select distinct ORDERID from (select top 100 percent ORDERID,ORDERDate from ORDER where ORDERID = '123456' order by ORDERDATE desc) as p
August 3, 2015 at 7:45 am
adonetok (8/3/2015)
I used code below, it works but I am not sure it is good.select distinct ORDERID from (select top 100 percent ORDERID,ORDERDate from ORDER where ORDERID = '123456' order by ORDERDATE desc) as p
That's not guaranteed to return the rows in the appropriate order.
If you're filtering by orderid and returning a single row from orderid, why would the date matter?
How many orderdates can an order have?
August 3, 2015 at 7:47 am
The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
August 3, 2015 at 7:48 am
adonetok (8/3/2015)
I used code below, it works but I am not sure it is good.select distinct ORDERID from (select top 100 percent ORDERID,ORDERDate from ORDER where ORDERID = '123456' order by ORDERDATE desc) as p
Again...how many orders have the same OrderID??? You are not providing any details for what is clearly something you need help with.
What does the following query return?
select count(*)
from ORDER
where ORDERID = '123456'
_______________________________________________________________
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/
August 3, 2015 at 8:00 am
The Count of ORDERID will be determined by run time.
In fact, table ORDER should name it as ORDERHISTORY.
Any adding, modifying orders will save to ORDERHISTORY using the same ORDERID.
What function in App will do is getting a ORDERID from ORDERHISTORY and then delete that Order.
But, I want to make sure this ORDER is created from the latest date and time.
August 3, 2015 at 8:07 am
Is the orders numbers are already apprear one after another wouldnt just selecting a MAX(orderid) from "your table" work?
August 3, 2015 at 8:12 am
No, max(ORDERID) did not work
August 3, 2015 at 8:12 am
adonetok (8/3/2015)
The Count of ORDERID will be determined by run time.In fact, table ORDER should name it as ORDERHISTORY.
Any adding, modifying orders will save to ORDERHISTORY using the same ORDERID.
What function in App will do is getting a ORDERID from ORDERHISTORY and then delete that Order.
But, I want to make sure this ORDER is created from the latest date and time.
Well there are a dozen or so possibilities in my head and they ALL depend on your table structure (which you still haven't bothered to post). If you really want some help then post the ddl, some sample data and the expected output. This type of thing is incredibly easy when you know how to do it but I am not guessing at your tables.
_______________________________________________________________
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/
August 3, 2015 at 8:41 am
Assuming a well designed database and order system, shouldn't the OrderId be ordered by OrderDate already, therefore, ordering by OrderID should produce acceptable results?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 3, 2015 at 9:00 am
Hmmm...I'm a little confused about the purpose of this code.
First, the WHERE clause filters results to rows that have a particular ORDERID, and then you SELECT DISTINCT ORDERID, so you know ahead of time you're either going to get zero rows (when no rows exist with that ORDERID), or one row with the value you pass to the WHERE clause for ORDERID. So, ultimately, you're only really finding whether rows exist with that ORDERID or not.
Second, since you're only going to return one value for ORDERID, why does it need to be sorted? The result set will be the same whether ordered or not, given the current query.
I'm guessing that the query you posted has been altered for presentation on the forum, and some key details were modified in meaning-changing ways. Otherwise this is very strange 🙂
Cheers!
August 3, 2015 at 2:51 pm
adonetok (8/3/2015)
I rand code below but got an error saidORDER BY items must appear in the select list if SELECT DISTINCT is specified
select distinct ORDERID from ORDER where ORDERID = '123456' order by ORDERDATE desc // error
I have to modify code to
select distinct ORDERID,ORDERDate from ORDER where ORDERID = '123456' order by ORDERDATE desc
But, I really need only "ORDERID".
How to make "ORDERDATE" not displaying?
If you are filtering on a single value as indicated in example above (ORDERID = '123456'), then it makes no sense to apply and ORDER BY to a DISTINCT result, because only (1) row will always be returned.
I'm assuming that what you're really wanting to do is return multiple distinct ORDERID and have the result ordered by ORDERDATE. What you can do is use GROUP BY instead of DISTINCT.
create table ORDERS (ORDERID int, ORDERDATE date);
insert into ORDERS values (1,'2015/05/02'),(2,'2015/05/03'),(3,'2015/05/01');
select ORDERID
from ORDERS
group by ORDERID
order by max(ORDERDATE) desc;
ORDERID
-------
2
1
3
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply