Order ID For Latest Order For Every Customer
Hi!
For the Orders table (let's assume for the Northwind database), I'm trying
to get the order id of the latest order for every customer.
That means that the result should be one record per customer and that would
display CustomerID and OrderID.
Any ideas?
Thanks,
Assaf
View Complete Forum Thread with Replies
Related Forum Messages:
Select Customer Last Inserted Order Details
Hi, Check this SQL SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending') AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2)) one customer can have more than one orders. So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
View Replies !
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following... SQL Server Express edition Visual Studios 2005 Jet 4.0 newest upgrade .Net Framework 2.0 (or should I use 3.0) VS2005 Security upgrade Anything else I need for just creating a database for my VS2005 Visual Basic project? I was trying to use MS Access as my backend db but would like to try SQL Express Thank you, Mark
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number. My table relationship is PurchaseOrder ->PurchaseOrderItem. below is a Stored Procedure that i have wrote in creating a PO: CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT) AS BEGIN INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID) END SET @POno = @@IDENTITY RETURN However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem? CREATE TRIGGER trgInsertPOItem ON PurchaseOrderItem FOR INSERT AS BEGIN 'What do i entered???' END RETURN help is needed asap! thanks!
View Replies !
Find Order By Date Range Or Order Id
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
View Replies !
Recordset's Order And Database's Physical Order?
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
View Replies !
Default Sort Order When Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek: Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons'); then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75)); select * from test_sort order by cast( description as nvarchar(75)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause. The sql generated is as follows SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth If I run sql the results are displayed in proper order but the view only order by first item in order by clause. Has somebody experience same thing? How to fix this issue? Thanks,
View Replies !
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
In-Order/Level Order Etc. Traversal Using CTE
Hi, I have some hierarchical data in a table. Say for example: Parent Child ------------------------ NULL 1 1 2 1 3 2 4 2 5 3 6 3 7 5 8 5 9 7 10 7 11 11 12 11 13 Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13... What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well. with Tree (id) as ( select id from WithTest where parent is null union all select a.id from Tree b join WithTest a on b.id = a.parent ) select * from Tree Any ideas? Thanks.
View Replies !
How To Load A Unicode File Into The Database In The Same Order As The File Order
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View Replies !
Force The &"ORDER BY&" To Be In Ascending Order??
I noticed the StockDate is not sorted in proper order, like ascending order... Code: select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd... The sample data is MM/DD/YYYY... So, how do I get it ordered propery by Year, Month then Day??
View Replies !
Sql Order By
Hi all, I had one question on sql statement. I had a table with a field named severity. The field severity will either consist of Minor, Moderate or Severe. How can I construct an sql statement whereby the severity will be order as Severe follow by Moderate and Minor. Thanks
View Replies !
WHERE + ORDER BY ?
Hello I am not sure of the correct syntax. I know that the first part works: ****************************** SELECT Extn, Domain_Name, Price FROM Domains_DB **************************** I am trying to add a WHERE clause is equal to com and an ORDER BY assending order. I have tried all sort of combinations, where am I going wrong with the following: SELECT Extn, Domain_Name, Price FROM Domains_DB [WHERE Extn = com [ORDER BY Domain_Name ASC ]] Thanks. Lynn
View Replies !
ORDER BY
hi, i' ve Drop Down List with sorted catagory and Data Grid that cange according to selected item in drop down list ... i need to send the selected item as value to SELECT statment, so i 've send (option) as a value "SELECT [userstory].* FROM [userstory] WHERE ([userstory].[rel_id] = @rel_id) ORDER BY @options " but there is an error: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name
View Replies !
Order By
Hi everyone,I have a select statement of the form SELECT * FROM temp ORDER BY timeI have a scalar function ConvertToMinutes that takes in varchar and returns int, is there any way to do something like this SELECT * FROM temp ORDER BY ConvertToMinutes(time). I tried doing this and it doesn't work (it tells me ConvertToMinutes is not a built-in function). Please guide me as to how I would accomplish this. Thanks in advance.P.S. Clarification: I am trying to order the table temp by the value returned by the function ConvertToMinutes on the coloumn time.
View Replies !
Order By??
Hi All, I have a question in sql.... How can i sort a select statement depending on nvarchar not on Int ??My select statement is : " select * from table1 order by st_name asc"can anyone help me? thanks a lot
View Replies !
Order By
How would i add order by to the syntax below:"Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &" and " & Box2i have tried adding it after Box2 but it doesnt work.Any ideas?
View Replies !
Is It Necessary To Order Again?
I have a function that returns a table:CREATE FUNCTION dbo.Example(@Param int)RETURNS @Tbl TABLE (Field1 int,Field2 int) ASBEGININSERT @Tbl (Field1,Field2)SELECT FieldA,FieldB FROM DataTableWHERE FieldC = @ParamORDER BY FieldARETURNENDThe statement that populates the table orders the data. In orderto ensure the results are ordered that way, should the call to thefunction include an ordering? I.e., is this sufficientSELECT * FROM dbo.Example(17)or is this necessary? --SELECT * FROM dbo.Example(17) ORDER BY Field1Thanks!
View Replies !
Order By
i want to use an order by clause. my issue is that the values i have are alpha and i need the top of the order to be 'A', 'S', then what ever. how can i sort this column and have the top two being 'A's and 'S's???? thanks in advance e
View Replies !
Order By
Was playing around with the order by to try and understand how it works in a union query. So l took a query that l've written before and modified it. When l run this query l get the following error. ORDER BY items must appear in the select list if the statement contains a UNION operator. SELECT Distinct Loan_no AS Loan_no, Date_Issued AS Date_Issued, Store AS Store, Product AS Product, Capital_Amount AS Capital_Amount, Interest_Amount AS Interest_Amount, Total_Amount AS Total_Amount, Insurance_amount As Insurance_Amount, Admin_Fee AS Admin_Fee, User_Issued AS User_Issued, LoanBook AS Company, Status FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000' UNION SELECT NULL AS Loan_no, NULL AS Date_Issued, NULL AS Store, NULL AS Product, Sum(Capital_Amount) AS Capital_Amount, Sum(Interest_Amount) AS Interest_Amount, Sum(Total_Amount) AS Total_Amount, Sum(Insurance_amount) As Insurance_Amount, Sum(Admin_Fee) AS Admin_Fee, NULL AS User_Issued, NULL AS Company, NULL AS Status FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000' ORDER BY CASE WHEN Loan_no IS NULL THEN 0 ELSE 1 END , Date_Issued,
View Replies !
BCP Out With ORDER BY ??
I need to BCP out from a table and guarantee the "ORDER BY" with a key column. When using BCP, are the records guaranteed to be returned in a specific order? If not, how can I specify? (BOL says selecting without an ORDER BY clause cannot guarantee order of result set.)
View Replies !
Order By
Table has two columns id, theme select id, theme from content_theme order by theme brings following result set 38 Alphabet 97 Animals 0 Any Theme 98 Architecture 3 Artists' Lives & Work 92 Autumn/Fall 39 Awards & Honors 4 Being Your Best 40 Birthdays 41 Boats & Ships Is there a way to display 'Any theme' 1st in the set and than display other in order?
View Replies !
Order By
my table consists of a column with the following records; T1 T1.1 T1.2 T2 T3 .. .. T100 In my query if I am sorting (order by) this column, the results are; T1 T1.1 T1.2 T10 T11 T2 T21 ... You got the idea! How can I sort it the right way?
View Replies !
Need Help With Order By
i got a huge query, and some counting columns. i want to order by a column that have been counted. i have used AS in this query but it doesnt work. i know that its nothing wrong in this query excpet that order thing. i hope you guys can help me Code: String sql = "SELECT Top " + Integer.parseInt(tVisaValda.getText()) + " Kund.Idnummer,Kund.Namn, (SELECT Count(Kundorder.Kundid) FROM Kundorder WHERE Kundid = Kund.kundid) AS antKop, (SELECT sum((Salda_artiklar.Pris+((Salda_artiklar.momssats/100)*Salda_artiklar.Pris))*Salda_artiklar.Antal) FROM Kundorder INNER JOIN Salda_artiklar ON Kundorder.Orderid=Salda_artiklar.Orderid WHERE Kundid=Kund.Kundid) AS inkMoms, (SELECT Sum(Salda_artiklar.Pris*Salda_artiklar.Antal) FROM Kundorder INNER JOIN Salda_artiklar ON Kundorder.Orderid=Salda_artiklar.Orderid WHERE Kundid=Kund.Kundid) AS exkMoms, Salda_artiklar.Skickade_datum AS skickadeDatum, (SELECT sum(Salda_artiklar.Pris*Salda_artiklar.Antal) FROM Kundorder INNER JOIN Salda_artiklar ON Kundorder.Orderid=Salda_artiklar.Orderid WHERE Kundid=Kund.Kundid) AS genom " + "FROM ( Kund INNER JOIN Kundorder ON Kund.Kundid = Kundorder.Kundid) " + "INNER JOIN Salda_artiklar ON Kundorder.Orderid = Salda_artiklar.Orderid " + "WHERE Salda_artiklar.Skickade_datum BETWEEN '" + tIntervalll.getText() + "' AND '" + tIntervall2.getText() + "' " + "ORDER BY antKop;
View Replies !
Order By
Hello, How will you use the order by in a column that distinguish in each string? say; id | letter 0 | ABC 1 | ABD 2 | ABE "select letter from table order by letter asc" using this puts the value ABD on top than ABC
View Replies !
Order By
Hi, I need some help with this query I have a table which saves data for athletes Tblsports AthName SchoolId SchoolName CoachName Gender TestDate Total So there will be 1000 records of different schools who particapate What i want to display is the School Name Coach Name Total The way it has to be is find the top 3 athletes in each school add their total and then find the 3 schools who got the highest total and order them so the highest school is on top todd
View Replies !
T-SQL Help With Order By
In a stored procedure MS-SQL I am trying to write an order by expression that is a function of the incoming paramters. SO for example CREATE PROCEDURE test @sortby varchar(10) AS SELECT * FROM table WHERE condition ORDER BY @SORTBY Gives an error (1008) but the error message implies that you can use a variable in the order by expression. I have tried every combination I can think of with the variable as varchar = column name, as integer . I have also tried variations of Order by COL_Name(OBJECT_ID('item"), variable) and I ether get errors or no errors but no order either. Any leads appreciated. TIA Mike
View Replies !
Order By
Is there a way to order data how you want besides Ascending or Descending? Example: I have M-F I need to sort in order of Monday-Friday.
View Replies !
Help With ORDER BY 1,2,1,2
i have this table fname val ---------------------- aaaaa 2 aaaaa 1 bbbbb 2 bbbbb 1 ccccc 2 ccccc 1 ddddd 2 ddddd 1 how to roder BY FNAME + VAL like this 1,2,1,2,1,2 always 2 is above 1 TNX
View Replies !
Order By
how do i order a varchar column so i get the result 1,2,3,4,5,6,7,8,9,10,11,A,B instead of 1,10,11,2,3,4,5,6,7,8,9,A,B
View Replies !
PK Order
I have a compiled and deployed to our clients site application, which in one case does not use ORDER BY clause in a sql statement. The assumption has been that SQL Server will always return the rows in ascending order of the PK (it is a composite PK). However, on some SQL Server machines the order is returned correctly, on others - not. I suspect that the order is defined by the way the indexing structure is built behind the scenes. The process of deploying patches to the clients' sites is difficult and very long. I am wondering if there is some SQL Server setting (stored procedure), which will rebuild the indexing structure in a way so that it uses the ascending order of the PK columns. Thank you, Nikolay
View Replies !
ORDER BY WHERE?
Is it possible to write a select statement that will order results descending by most matches of a column value? eg. i have a table with an object.. including an id number.. a second table with comments on the object.. holds the id number as well. i want to return the object with the most comment rows first.
View Replies !
Order ..
Hi I want the order of this data to come back in the same order of the fields in the IN clause select name,code from TabA where name in ( 'text1' , 'text2' 'more text' . . . ) Is there a way of doing this ? it will save a whole lot of time ?
View Replies !
Order By DAY
How to sort day of week starting by Monday? select first_name name, hire_date , TO_CHAR(hire_date, 'day') Day from employees ------------------------------... my above query is working well except that I want to sort the rows order by day of week starting from Monday.
View Replies !
Order By
hi all, i failed to find any topic related on what im trying to do :- how do i order by column1 (that is varchar column) with <NULL> at the bottom without DESC ? a b c <NULL> <NULL> NOT <NULL> <NULL> a b c thanks ~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
View Replies !
Order Top 1
Hi all, How do i select first top 1 id from given tblA.. so that multiple records for 1.MM06707003-3Waiting finance 2.MM06707003-4complete 3.MM13707001-4complete will return only first one distinct adjref record that is 1.MM06707003-3Waiting finance 3.MM13707001-4complete should i order distinct top 1? -- Prepare sample data DECLARE@tblA TABLE (Ref varchar(12),Client varchar(12), Submit char(1), CN char(1), GM char(1), FN char(1)) INSERT@tblA SELECT'MM06707003', 'SURETEX','Y','Y','Y','N' UNION ALL SELECT'MM06707003', 'SURETEX','Y','N','N','N' UNION ALL SELECT'MM13707001', 'SURETEX','Y','N','N','N' --my workaround SELECT distinct Ref, Client, Submit, CN, GM, FN, CASE WHEN Submit='Y' AND CN='Y' THEN CASE WHEN GM='Y' AND FN='Y' THEN '4-Complete' WHEN GM='Y' AND FN='N' THEN '3-Waiting Finance' WHEN GM='N' THEN '2-Waiting GM' END WHEN Submit='Y' AND CN='N' THEN '4-Complete' WHEN Submit='N' THEN '1-Incomplete' END AS Status FROM @tblA order by status --expected results SELECT'MM06707003', 'SURETEX','Y','Y','Y','N', '3-Waiting Finance' UNION ALL SELECT'MM13707001', 'SURETEX','Y','N','N','N', 'Complete' ~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
View Replies !
ORDER BY
Hi all... Have a problem in sorting using a variable. DECLARE @sortItem varchar(max) SET @sortItem = 'empno' SELECT * from hrEmployees ORDER BY @sortItem ASC Is it not possible? How could I sort using a variable? -Thanks -Ron- "A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."
View Replies !
Order By
Hi all, I am facing one problem with order by in a sql query statement. Let me tell in brief:- I have two fields in my table Sent_DT (varchar as datatype) Sent_Time (varchar as datatype) These two fields are storing date and time respectivly For eg:- Sent_DT = 30/01/2007 and Sent_Time = 04:02 Now I need to use order by for this two fields. For this purpose I wrote one query "select * from Event_Messages order by Sent_DT DESC, Sent_Time DESC" But this query is not giving the correct answer. So is there any way to concordinate these two fiels or to convert it to datetime. Please help me ! Shaji
View Replies !
Order By
hi all, ive no idea how to perform this order by query.. i want to sort by qty, but at the same time i want to categorized by tID.. if i order by qty, the tID will not stay in group.. here's my sample data :- (i want to sort by qty when Flag='AVA', the rest tID will follow the 'AVA' tID) DECLARE@tbl1 TABLE (tID varchar(12), qty float, flag varchar(5) ) INSERT@tbl1 SELECT'A', null, 'AVA' UNION ALL SELECT'A', '2', 'PICK' UNION ALL SELECT'B', '1', 'AVA' UNION ALL SELECT'B', null, 'PICK' UNION ALL SELECT'C', null, 'AVA' UNION ALL SELECT'C', '1', 'PICK' select * from @tbl1 order by qty ---expected result DECLARE@tblResult TABLE (tID varchar(12), qty float, flag varchar(5) ) INSERT@tblResult SELECT'A', null, 'AVA' UNION ALL SELECT'A', '2', 'PICK' UNION ALL SELECT'C', null, 'AVA' UNION ALL SELECT'C', '1', 'PICK' UNION ALL SELECT'B', '1', 'AVA' UNION ALL SELECT'B', null, 'PICK' select * from @tblresult ~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
View Replies !
Order By
hey all.. how do i order column date with null at the bottom? eg: date 1 jan 99 2 jan 99 NULL Not like normal :- NULL 1 jan 99 2 jan 99 thanks in advance ~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
View Replies !
New Order
I hav sql query analyser 2002,I have a table t1: mechantorder type a1new a2renewal a3renewal a4renewal a5renewal a6new a7new a8renewal a9renewal a10renewal a11renewal …… b1new b2new b3new c1new c2renewal c3renewal c4renewal … d1new d2new d3renewal d4renewal d5renewal Based on t1, I want to add a new colunm [new order] mechantorder typenew order a1new1 a2renewal2 a3renewal3 a4renewal4 a5renewal5 a6new1 a7new1 a8renewal2 a9renewal3 a10renewal4 a11renewal5 …… b1new1 b2new1 b3new1 c1new1 c2renewal2 c3renewal3 c4renewal4 … d1new1 d2new1 d3renewal2 d4renewal3 d5 renewal4 How can i acheive that? Thank you very much. Jeff
View Replies !
Order By
Hello ! i am just moved in to SQL form access. in my first project i use an adodc to connect to the SQL database. I have used the following code in vb6 to access a Table : adomain.RecordSource = "select * from tbmain order by Name" I Found 'order by Name' is not working in SQL My question is what is the alternative to the above code when using MS SQL. Thanks
View Replies !
Need Help In Order By
Im trying to select the 5 top documents in the research_downloads table and order them by greatest number desc. The query below is returning the recordset ordered by content_title. Should I be using a count() in the select instead of the TOP and use the count() in the Order By? SELECT Distinct Top 5 [content].content_id, [content].content_title FROM research_downloads INNER JOIN [content] ON = [content].content_id WHERE [content].folder_id=30 AND CHARINDEX('<PrimarySubjectArea>Content</PrimarySubjectArea>', content_html) > 1 Group By [content].content_id, [content].content_title, research_downloads.insert_date ORDER BY ??
View Replies !
|
TRACKER
