Order By Query

Jun 14, 2007

Hi, I am using below query: 

SELECT     tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body,
                       tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed,
                       tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating,
                      tbh_Articles.ImageURL, tbh_Articles.special
FROM         tbh_Lang CROSS JOIN
                      tbh_Articles
WHERE     (tbh_Lang.LangID = @LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR
                      tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR
                      tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR
                      tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR
                      tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR
                      tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5)


Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...

Tables structure:  tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2.... )

 Any suggestions?

 

 

 

View 9 Replies


ADVERTISEMENT

Order By Query

Sep 29, 2007

I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment.


Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer.


How do I build this Query?

View 7 Replies View Related

SQL Query Or SP? Order Value Between Two Dates

Feb 7, 2008

Hi all,

Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!

Table Structure (abridged, relevant columns):

Orders:

Code:

[Orders](
[OrderID] [int] IDENTITY(1,1)
[OrderDateTime] [datetime]
[OrderSiteID] [nvarchar](255)
[OrderOffline] [bit]
[OrderSentToWP] [bit]
[OrderReceivedFromWP] [bit]
[OrderAuthorised] [bit]
[OrderCancelled] [bit]
[OrderApproved] [bit]
[OrderFraud] [bit]
[OrderDispatched] [bit]


OrderItems:

Code:

[OrderItems](
[OrderID] [int]
[ProductID] [nvarchar](255)
[Quantity] [int]
[Price] [real]
[Weight] [real]


Products:

Code:

[Products](
[ProductID] [uniqueidentifier]
[ProductCode] [nvarchar](255)
[ProductTitle] [nvarchar](255)



Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.

I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.

So far I have this:

Code:

SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID
WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND
(Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND
(Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid'
GROUP BY Products.ProductCode, Products.ProductTitle



Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again

I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!

Thanks in advance to anyone that looks at this for me.

Also, any recommended books/sites to learn this kind of query?

Richard

View 4 Replies View Related

Parsename Query Order

Jun 5, 2008

Greatful for any help....

Have the query below which is taking delimited address information from _Venue column. This works well apart from the order it is returned, for example, the output below has the address tittle displayed in a different column for each row

Queen Elizabeth's Hunting Lodge is in Address1
All Saints' Church is in Address2
Audley End House is in Address3

As I need to reference from the query the correct part of the address from the same location each time, is there anyway to get around this?

Thanks in advance


SELECT coalesce (PARSENAME(REPLACE(_Venue,',', '.'),4), '-') address1
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),3), '-') address2
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),2), '-') address3
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),1), '-') address4
FROM table



Address1 Address2Address3Address4
----------------------------------------------------------------------------------------
Queen Elizabeth's Hunting LodgeRangers RoadChingfordLondon E4 7 QH
- All Saints' ChurchShrub End RoadColchester
---Audley End House

View 10 Replies View Related

How To Get Row From Select Query In Order

Jan 20, 2014

I have table called 'UserDetails'. If I execute below select query it should display in order of uno= 7,13,5 but i get in order of

uno=5,7,13.

How to get in order of uno= 7,13,5

Select EmailAddress,EmployeeName,UNo, MobileNumber
from UserDetails where (UNo=7 or UNo=13 or UNo=5 ) group by uno,emailaddress,employeename,uno,mobilenumber

Result I am getting as

EmailAddress EmployeeName UNo MobileNumber
-----------------------------------------------------------
aaa@xxx.com ravi 5 8989898989
bbb@xxx.comramesh 79898989898
ariv@gmail.com arivu 13 8989898989

View 3 Replies View Related

How To Get Order Values In Sql Query

Nov 24, 2006

Hi every body.
Can u tell me how to get the order values of the SQL query
Example.
My sqlstring ="Select * from tbl_Products"
And it returns 6 rows
And I want to get order values like this 1,2,3,4,5,6
I am a beginner.
Thanks a lots

View 1 Replies View Related

Slow Query When You Add An Order By

Feb 20, 2008

SELECT Column1,Column2,Column3 ....
FROM vwMyView
ORDER BY CreatedDT

View has about 10000 rows, If I remove order by query runs faster but adding an order by cause query to timeout..

All tables have clustered index based on the primary key of the table..

(a) Should I create an index view with CreatedDT as non clustered index?
(b) Or create a non clustered index on CreatedDT column on the underlying table?

I can provide DDL but if something obvious I am missing

Thanks

View 5 Replies View Related

SQL Query Help-- Order By Clause

Jul 20, 2005

HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks

View 7 Replies View Related

Order Of Conditions In A Query

Nov 6, 2006

I have a query with many (approximately, 30) conditions, such as:

select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) )

My question is:

In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement.

The question is, is there the same rule in T-SQL?

I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")?

This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance...

By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan.

[Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script?

We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.]

Could someone kindly advise me on this, please?

Thank you ever so much.

View 4 Replies View Related

Need An Special ORDER BY Clause Query

Feb 25, 2008

Table:ColumnsUsersList:UserID, UserName, Country
I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order
So the query first return all the users from Pakistan and the the users from rest of the world in ascending order.
Forexample,
1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan
then the query returns.
3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA

View 5 Replies View Related

Multiple ORDER BY Query Syntax

Mar 24, 2004

I have some data that I'd like to order by a certain attribute.. but if there is a tie, then it should order by a secondary attribute.. and if there's still a tie.. then a 3rd attribute.

Currently the query looks like this:

SELECT * FROM Players WHERE ORDER BY Points

But I want it to look something like this (I know this doesn't work.. but it's just to give an idea):

SELECT * FROM Players WHERE ORDER BY (Points desc AND Games asc AND Goals desc)

Does anyone know the proper syntax for a multiple ORDER BY query like I described above?


Brent

View 2 Replies View Related

How To Make A Random Order Query ?

May 1, 2004

If we use: select * from ..... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?

View 1 Replies View Related

Force Order Query Hint

Jun 8, 2001

We are discovering that adding Force Order to a query is substantially increasing performance. Any issues around using this ?

Craig

View 1 Replies View Related

Using An Array To Determine Query Order??

Oct 17, 2006

Hi.

I have a VB.NET function that returns an array of Integers.
Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54]
One characteristic of the array is that no two numbers
repeat - it reflects the IDs of my Users table. And, that
it is not ascending or descending.

What I would like to know is how do I sort my query in
the order of the integers in this array? Ideally, I
would like to use ORDER BY for this query.

thanks in advance.

View 2 Replies View Related

Is Order By Affect The Query Speed

Aug 7, 2007

hello,

I have a query that insert insert into new table , and then i select from this table,

if i add ORDER BY in the INSERT INTO script , does it affect the speed of the SELECT


i have big table that take about 70 sec

View 14 Replies View Related

UPDATE Query With ORDER BY Clause

May 7, 2012

I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.

Code:
SELECT * FROM DMTM
SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223'
WHERE temp_pk in

[code]...

View 6 Replies View Related

[ Resolved ] Union, Each Query W/ Own Order By

Oct 10, 2006

I am trying to combine 2 queries, each with their own 'order by' and I am having trouble.

This is just an example, not what I'm trying to do, my query is more elaberate but looks simular to this.

SELECT TOP 10 *, 'FieldA' AS SortedBy
FROM TableA
Order By FieldA Desc
UNION ALL
SELECT TOP 10 Precent *, 'FieldB' AS SortedBy
FROM TableA
Order By FieldB Asc


Anyway not to get the following error?
Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'UNION'.

View 2 Replies View Related

Tricky Group By Order By Query

Jul 23, 2005

Hello, I'm trying to find the most optimal way to perform a trickyquery. I'm hoping this is some sort of standard problem that has beensolved before, but I'm not finding anything too useful so far. I havea solution that works (using subqueries), but is pretty slow.Assume I have two tables:[Item]ItemID int (Primary Key)ItemSourceID intItemUniversalKey uniqueidentifierPrice int[Source]ItemSourceIDPriorityI'm looking for a set of ItemIDs that match a query to the Price(something like Price < 30), with a unique ItemUniversalKey, taking thefirst item with each key according to Source.Priority.So, given Item rows like this:1 2 [key_one] 152 2 [key_two] 253 1 [key_one] 15and Source rows like this:1 12 2I want results like this:2 2 [key_two] 253 1 [key_one] 15Row 1 in Item would be eliminated because it shares an ItemUniversalKeywith row 3, and row 3's Source.Priority is lower than row 1.Help!?

View 5 Replies View Related

Query Performance With Order By Clause?

Jul 20, 2005

Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau

View 1 Replies View Related

Getting Order Number Into Query Grouped By Something

Apr 30, 2008

I have a table of transaction that includes student ids and dates. I need to select all records from the table and include a new value that is the sequential transaction numbered for each student with the oldest transaction for each student being numbered one, the next oldest numbered two and so on. So the result should look like student1, 10/1/2000, 1, student1, 10/15/2000, 2, student1, 2/12/2001, 3, student2, 9/1/1999, 1, student2 10/2/2000, 2, student2 , 12/15/2000, 3, student2, 11/4/2001, 4 and so on.

View 8 Replies View Related

I Wanna Query A Purchase Order Table!...but Can Not:(

Feb 28, 2006

i use microaccess create table, there is a filed call"Complete_PO", value"yes/no"
i wrote following statement to select it, but at runtime, there is warning message"...constraint...one or more row violating non-unique and so so..." how to solve it
SqlSelectCommand2.CommandText = "SELECT Complete_PO FROM [PURCHASE ORDER] WHERE [PO_No] Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No,4) desc"
PoNum_SqlDataAdapter.Fill(PO_DataSet1)
TextBox1.Text = PO_DataSet1.Tables("PURCHASE ORDER").Rows(0).Item("Complete_PO").ToString()

View 5 Replies View Related

Purchase Order Query: Very Funny And Challenge ^_^

Mar 1, 2006

i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004
i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number
SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC"
i checked my database, last record is LP-0545381-0300
in my debuging process, surprisingly found that selected record is LP-0545381-301   !
any one hav any suggestion? ^_^

View 2 Replies View Related

Query Doesn't Order Once Where Clause Is Added

Sep 1, 2012

In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.

I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope works. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.

View 9 Replies View Related

Query Multiple Tables - Union/Order By

Oct 25, 2007

Hi!

I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?

Thanks so much

select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1

View 4 Replies View Related

Order Converted Dates In Union Query

Jun 15, 2006

I have the following as part of a union query:

CONVERT(CHAR(8), r.RRDate, 1) AS [Date]

I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.

Thanks,

Dave

View 1 Replies View Related

Transact SQL :: Order Of XML Nodes In Xpath Query

Nov 20, 2015

I have an XML field with some data that looks about like this:

<steps>
<step userName="abc"></step>
<step userName="abc"></step>
<step userName="saf2"></step>
<step userName="abc"></step>
</steps>

I have a query something like this:

SELECT XmlField.value('(//step/@userName)[1]', 'VARCHAR(100)') AS userName
FROM theTable

I would like to have some kind of indicator in my TSQL results of the number of each <step> node.  For instance, saf2 would be a 3.  I think this is similar to a ROW_NUMBER() kind of function in normal table data.

Is there a good way to get a 1, 2, 3, and 4 value for this sample data?

View 2 Replies View Related

Display Multiple Items On One Line Per Order (was Query)

Jun 27, 2005

I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.

View 1 Replies View Related

MS SQL Query, What's The Default Order The Rows Returned Are Sorted By?

Jan 12, 2005

i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in

View 6 Replies View Related

Query To Return All Procedure Calls In Order For Timerange?

Jun 25, 2014

I have a query using the sys.dm_exec_query_stats that returns the last exec of all proc calls for the last day and execution times.

Is there a way to get all the executions and not just the last w/o using a db trace?

View 1 Replies View Related

ERROR: The Nested Query May Be Missing An ORDER BY Clause.

Aug 19, 2006

Hi,

on executing the below query i am getting the following error

ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.

even though the order by clause is presenet in the nested query

SELECT t.[ProductId], Predict ([Association].[Product Basket],3)
From
[Association]
PREDICTION JOIN
SHAPE {
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
APPEND
({OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
RELATE [ProductId] To [ProductId]
)
AS
[Product] AS t
ON
[Association].[Product Id] = t.[ProductId] AND
[Association].[Product Basket].[Product Id] = t.[Product].[ProductId]

View 5 Replies View Related

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?

Mar 22, 2008

Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma

View 10 Replies View Related

Create A Query That Will Give Result Set Containing Primary Order On Type

May 14, 2012

I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type

Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)

But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)

Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)

View 7 Replies View Related

Server Query Execution - Where Filtration Order - Performance Impact

Jul 27, 2015

For example in a Select Statement we have many tables and we have Where Clause with many conditions with AND operations. Do the SQL SERVER would apply the Where clause after all fetch or can dynamically decide about to include the related Tables from Select Statement Orderly with respect to where clause predicates? (SQL SERVER would not fetch data of those tables for its Select, where the AND condition in Where clause fails or by logic would be fruitless/not-related.)

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved