Sorting The Result

Apr 24, 2006

If this is in the wrong group lemme know and I'll re-post it to the
correct one. I am using PHP and MySQL.

Suppose I have a table named "servicios" with 5 columns and 200 rows.

If I want to get the whole table sorted by column 3 (suppose the field
name is "nf") I use:

SELECT * FROM `servicios` ORDER BY `nf` ASC

This workd perfectly.

If I want to get just the first 10 rows with _no_ sorting I would use:

SELECT * FROM `servicios` LIMIT 10

This also works perfectly.

Now, if I want to get the first 10 rows, sorted by "nf", I use:

SELECT * FROM `servicios` ORDER BY `nf` ASC LIMIT 10

This does NOT work perfectly. The result I get is the first 10 rows of
whole table which has been sorted by "nf". What I want is just the
resulting first 10 rows to be sorted by "nf", not the whole table.

How can I do this?

Thanks,
Philippe

P.S. I am very new to MySQL, please forgive any unforgivable begginer
mistakes.

View 2 Replies


ADVERTISEMENT

SELECT Result Without Index Sorting

Mar 16, 2004

I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.

View 14 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.



Any suggestions would help.


Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int.  When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end.  The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order.  The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

Return Subquery Result For Only First Row In Result

Apr 7, 2015

I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.

declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday

select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],

[code]....

In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.

View 6 Replies View Related

Strange Result - Minus Result -1

Mar 2, 2008

help strange result whan i do this



Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList




i try to get next unit value to next month
why i get this -1
on date




01/01/2008
1
-1

unit_date unit new_unit



01/02/2008
2
1

01/02/2008
1
4

01/01/2008
1
-1

01/02/2008
1
4

21/01/2008
1
-1

21/01/2008
1
-1

01/02/2008
1
4


TNX

View 3 Replies View Related

SORTING

Apr 10, 2006

Ok this is what I have
$query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";

But what I want is to return all results based on the file name from the most recent to the oldest. Can anyone help????

View 7 Replies View Related

Sorting

Mar 27, 2008

hi,

i need a query based on sorting.
i have date column and time as varchar in another column and other order details

datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1

so i need to get query o/p as

12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm

based on date and time. pls help!

View 6 Replies View Related

Sorting Twice?!

Jun 27, 2006

here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim

View 3 Replies View Related

Sorting

Oct 11, 2006

I have a text field in the form of numbers separated by periods like this: 1,1.2,1.2.1

When I sort, it works fine unless I get out to 10 digits.

Desired sort: 1,1.1,1.1.1,1.1.2,.....,1.1.9,1.1.10

Actual sort: 1,1.1,1.1.1,1.1.10,1.1.2,.....,1.1.9

The 10 is showing up smaller than the 2 because of the straight text comparison, any ideas on how to get around this?

View 6 Replies View Related

Gridview Sorting

Aug 17, 2006

I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc.  So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this.  For only ASC sorting, I've got
ORDER BY  CASE WHEN @sortBy='' THEN DateCreated END,  CASE WHEN @sortBy='DateCreated' THEN DateCreated END
and so on.  However, columns can also be sorted with DESC.  I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC.  How can I do this?

View 2 Replies View Related

Problem With Sorting

Jun 25, 2007

I am trying to set up custom paging and sorting with my gridview. All is well but the sorting. The problem is with the stored procedure.  If I pass in the value @sortExpression as, for example "discussions_Posts.post_time", i does not sort it at all. But if I replace the @sortExpression with discussions_Posts.post_time in the actual stored procedure, it gets sorted. how do I sort this query with a input parameter with values like "discussions_Topics.topic_title" or something?  ALTER PROCEDURE discussions_GetTopicsSubSet@startRowIndex as int,@maximumRows as int,@sortExpression as nvarchar(50),@board_id as intASDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,topic_time DATETIME,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)--DECLARE @TopicsFrom Datetime--SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END-- populate the table  CAST(getdate() as int)INSERT INTO @TopicsSELECT     ROW_NUMBER() OVER (ORDER BY  @sortExpression), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type,discussions_Topics.topic_time, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username,                      user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM         discussions_Topics INNER JOIN                      discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN                      aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN                      aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE     (discussions_Topics.board_id = @board_id ANDdiscussions_Topics.topic_type NOT LIKE '1' )SELECT * from @TopicsWHERE RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1 

View 1 Replies View Related

Need To Do Some Tricky SQL Sorting

Dec 13, 2007

This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.
I have a table that has 2 columns of particular interest for the purposes of this question.  One is a foreign key to another table (int), the other is a name (varchar(50).  I want to sort the results set in a specific way.  I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name.  I was trying to be cute and tried an order by statement like this:  "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be).  I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).

View 2 Replies View Related

Sorting By Date And Then Sorting By Name (within Date)

Jan 7, 2008

Hi,
 I hope I can explain what I am trying to achieve (in MS SQL Server):
 The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name.
 I have tried:
 ORDER BY date, surname, forename  (gives dates in right order but not names)
and
 ORDER BY surname, forename, date  (gives names in right order but not dates)
 
Thanks for any advice!
 

View 2 Replies View Related

Sorting And Performance

Feb 26, 2004

I noticed when looking at the execution path that the SQL sort (order by) was taking up 16% of the total for the query. However the execution time for the sort was like .007 ms in SQL so I doubt it was really that much overhead.

Is it more effecient to sort a dataset within my asp.net program?

View 5 Replies View Related

Sorting Question

Aug 2, 2004

Hi,

i have a simple table with NAME and a SORTORDER. Now i want to sort my table on the NAME desc and then insert a number into the SORTORDER. The number has to correspond with the number the NAME has in the list.

EXAMPLE

Initial situation:
BAKKER 0
AKKER 0
CARNIE 0

Now my question is how can i update the table with a sp in wich te outcome is:

AKKER 1
BAKKER 2
CARNIE 3

Can someone please help me?

Greetings,

Mark

View 3 Replies View Related

Sorting Question

Aug 11, 2004

i have a question that is there any methods to order by the column which's datatype is ntext with t-sql?

View 1 Replies View Related

Lexigraphical Sorting

Apr 3, 2006

Anybody know how to accomplish lexigraphical sorting? Any how-tos, docs, tuts or if you just want to paste code
 
THanks!

View 2 Replies View Related

Sorting By UserID

May 24, 2006

I'm trying to sort by domain user id, I can pull the user id fine but now I want to sort my SELECT by that name. How do I put the value of getUserIdentity into my SELECT statment.
Thanks
<script language="C#" runat="server">    protected string getUserIdentity()    {        return HttpContext.Current.User.Identity.Name.ToString().Replace("DOMAIN\", "");    }
    protected void Page_Load(Object sender, EventArgs e)     {        username = getUserIdentity();                SqlConnection myConnection = new SqlConnection("Data Source=CLIENTELE;Initial Catalog=forms;Integrated Security=True");        SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM formTable WHERE userID = @username ORDER BY status DESC", myConnection);
        DataSet ds = new DataSet();        myCommand.Fill(ds, "names");
        MyDataGrid.DataSource=ds.Tables["names"].DefaultView;        MyDataGrid.DataBind();    }
</script>

View 1 Replies View Related

Please Help!! - Sorting Problems

Sep 24, 2001

hi,

i am trying to figure out how to sort the data in a column in an ascending way when the column contains numeric and char data.
In the example I want to sort by category,lookup value. However, as you can see the top two rows are sorted right but the others are all messed up.
I would really appreciate any hints you can give me.
thanks
Rozina

Category ID lookupvalue

CPU Type4CPU Type 1
CPU Type4CPU Type 2
CPU Number51
CPU Number516
CPU Number52
CPU Number532
CPU Number54
CPU Number564
CPU Number58

View 1 Replies View Related

Sorting Records??

Jul 7, 2000

I have a table with a composite primary key (ie a,b, autoInteger)
that includes an auto-incrementing system generated integer.

How do I sort out the records such that if a and b above repeat, I only
get the records with the highest autoInteger?


Thanks for any suggestions
Shelly

View 1 Replies View Related

Sorting Dates

Sep 8, 1999

I need help sorting a date field. My field looks like this: dd/mm/yy. I want to sort by month. How would I do that?
Any help would be appreciated...

View 1 Replies View Related

Sorting Views

Jul 18, 2005

Hiya,

I'm back with another question.

I have a view of a table which contains users. the SQL is:

CREATE VIEW dbo.vwTravellers
AS
SELECT TravellerID AS ID, LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName)) + ' - DOB:' + LEFT(RTRIM(DOB), 11) AS Value
FROM dbo.tblTravellers

I would like to be able to sort the view by LastName, Firstname, but it doesnt allow a SORT BY in the view. Does anyone know of a cheeky little trick I could use to get around this?

Cheers
Guy

I wish I'd listened in that SQl Server training course I attended :(

View 12 Replies View Related

Sorting A Matrix

May 19, 2006

I am trying to sort a matrix by column. I first tried adding an interactive sort to the column heading, but it either gave me a run-time error, or there was not response. I then tried adding sorting to the row groups, and that doesn't seem to work either. I feel I have done every combination of groupings and sort expressions. Has anyone been able to accomplish this?
Thanks,
Kris

View 1 Replies View Related

Sorting With NULLs

Dec 29, 2004

Hi,

I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:

mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"

So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!

View 3 Replies View Related

Sorting Dates

Mar 3, 2005

Generally speaking, say using normal SQL if u wanted to sort month names in correct order how would u do it???

View 7 Replies View Related

Sorting Question

Nov 6, 2006

hi all..just a quick question about sorting a query. let us say that i am running a select statement that involves 3 or 4 tables and one of the columns in one of the tables is a divisions column. based on the records, there is a specific division that i want to appear first in the list. its not alphabetically possible nor is it the keys associated with it allow it to be selected first. how would i go about pulling that particular record from the divisions column to be listed first in my query? thanks alot

tibor

View 12 Replies View Related







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