Sort of Like Distinct


Is there a way to retrieve a set number of duplicate results

For example, I have a calendar application and I want to retrieve only 4 results per day.

The data is like this:

Woo, 6
Yes, 6
Foo, 6
Bar, 6
Yay, 6

where those 5 entries are from the 6th of the month, so basically I want to limit each distinct date to 4 results.

Any clues
Posted On: Monday 29th of October 2012 06:16:55 AM Total Views:  66
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




ORDER BY doesn't sort as expected

Hi Would anyone be able to look at the following statement and tell me why ORDER BY is not behaving as expected. Code: SELECT RTeamName, SUM( RGamePld ) , SUM( RGameWon ) , SUM( RGameLost ) , SUM( RGameDraw ) , SUM( DLegFor ) , SUM( DLegAgainst ) , SUM( RPointsSum ) FROM LeagueTable WHERE RDivision = 'A' GROUP BY RTeamName ORDER BY RPointsSum DESC LIMIT 0 , 30 of the Database fields are correctly spelt and the database has two records in for each team. The results displaying in the SUM() fields is correctly calculated but is not sorting by RPointsSum. The team with the most points is currenty halfway down the table. Any suggestions why this isn't sorting so that the team with two points is at the top of the table Many
VIEWS ON THIS POST

153

Posted on:

Wednesday 24th October 2012
View Replies!

Help with Gallery (sorting by categories, desc, max 15)

, I have a portfolio and for ease, I have created a database so I can just use a mysql query to show multiple thumbnails and other such features. I have about 5 categories (ie. websites, illustrations, stationary, etc.) I want to display 15 thumbnails per category on one page (the portfolio page), in order of newest to oldest. If a category has more than 15 thumbnails, I want to have a 'view all' link in which it would take you to a page such as category.php and show all the thumbnails in that category (which I'm sure I can figure out how to do on my own). how I want my portfolio page to look: Portfolio welcome, please check out my work. websites 15 14 13 12 11 10 09 08 07 06 05 04 03 02 01 > View all illustrations 15 14 13 12 11 10 09 08 07 06 05 04 03 02 01 > View all stationary 15 14 13 12 11 10 This is my table structure: Code: CREATE TABLE portfolio ( ID int(3) default NULL, Category varchar(25) default NULL, Title varchar(50) default NULL, ThumbAltTitle varchar(150) default NULL, AltTitle varchar(150) default NULL, Width int(4) default NULL, Height int(4) default NULL, ImageSize int(5) default NULL, Description varchar(350) default NULL, DateAdded varchar(30) default NULL, ThumbPath varchar(50) default NULL, FilePath varchar(50) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; you can ignore some fields, most of them I just need to create the img tag for the thumbnail and full size image, but I figured I would include it. This is what I have so far: Code: // Initialize Some Variables $id = $_REQUEST['ID']; $cat = $_REQUEST['Category']; // Access Settings $hostname = 'localhost'; $dbname = 'dbname'; $user = 'user'; $pwd = 'pwd'; // Conect to Database $connect = mysql_connect($hostname, $user, $pwd) or die ("Error: No connection to MySQL server\n"); mysql_select_db($dbname,$connect) or die ("Error: MySQL database not selected\n"); // Database Results mysql_select_db($dbname, $connect); $query = "SELECT * FROM portfolio ORDER by id='$id' LIMIT 15 DESC"; $result = mysql_query($query, $connect) or die(mysql_error()); $total = mysql_num_rows($result); // Classic C-Style Syntax For Loop: Initialize; Test; Increment for ($i = 0 ; $i < $total ; $i++) { $row_result = mysql_fetch_array($result); // Database Rows $imgid = $row_result["ID"]; $category = $row_result["Category"]; $title = $row_result["Title"]; $thumbalttitle = $row_result["ThumbAltTitle"]; $alttitle = $row_result["AltTitle"]; $width = $row_result["Width"]; $height = $row_result["Height"]; $imagesize = $row_result["ImageSize"]; $description = $row_result["Description"]; $dateadded = $row_result["DateAdded"]; $thumbpath = $row_result["ThumbPath"]; $filepath = $row_result["FilePath"]; // Products Looping Array for vectors echo ''.$title.''; } > What happens: 15 thumbnails show in ascending order, not descending (because i'm not sure how to do this) and it is not sorted by categories (because I haven't set that up because I'm unsure how). So basically, how can I manipulate what I have so I can have a page with my categories and thumbnails (max of 15) per category displayed with a link to view all if a category exceeds 15 thumbnails (with thumbnails showing descending).
VIEWS ON THIS POST

117

Posted on:

Wednesday 24th October 2012
View Replies!

LIFO - last in first out & using filesort problems in mysql

LIFO - last in first out & using filesort problems in mysql * How to improve table structure & query better, 'Using filesort' not happend mysql 4.0.21 table ad( siteid int primary key sitetitle varchar(30) sitedescription text sitedate datetime ) index sitedate data: select * from ad result: siteid , sitetitle , sitedescription , sitedate 1 , keyboard, 3 items , 2006-05-16 17:07:46 2 , motherboard , nothing , 2006-05-17 17:07:02 3 , mouse , stock , 2006-05-17 17:07:49 4 , computer , expired , 2006-05-18 07:01:46 . . . if table record are 2000000 then explain select * from ad order by sitedate then 'Using filesort' happend so the query very slow (20 seconds with 30 connection) * How to improve table structure & query better, 'Using filesort' not happend * How to improve table structure & query better, then query display as LIFO - last in first out without, using order by * How to improve table structure & query for LIFO whenever update to sitedate field exp : select * from ad where siteid in (3,4) the result: siteid , sitetitle , sitedescription, sitedate 4 , computer , expired , 2006-05-18 07:01:46 3 , mouse , stock , 2006-05-17 17:07:49 * if I do: update ad set sitedate = '2006-05-19 07:01:46' where siteid=3 result exp : select * from ad where siteid in (4,3) the result: siteid , sitetitle , sitedescription , sitedate 3 , mouse , stock , 2006-05-19 17:07:49 4 , computer , expired , 2006-05-18 07:01:46 Thank you all people
VIEWS ON THIS POST

192

Posted on:

Wednesday 24th October 2012
View Replies!

Ordering output, custom sort

I originally posted this in the PostgreSQL area but it didn't get a response so I thought I'd post it here since the MySQL area gets more traffic (and it's sort of a general SQL question anyway). I have a query: SELECT new_id FROM list WHERE old_id IN (132, 147, 339...lots and lots of id's...498, 938, 372); I would like the output (new_id) to be ordered in relation to the old_id's (as listed in parentheses), but instead the output just seems to be in some arbitrary order.
VIEWS ON THIS POST

97

Posted on:

Wednesday 24th October 2012
View Replies!

Using filesort...hmmm

I ran an explain on my Select statement and the extra returned "using filesort", even though I am sorting by the left column in a 2 column index. I couldn't seem to fix that, so I read about ALTER TABLE ... ORDER BY. Since the table is fairly static, I created an ALTER TABLE ORDER BY column. When I ran the select statement without the explain, it ordered it correctly without an order by clause. When I ran the explain with the new select, it didn't show filesort any logner, but when I added ORDER BY column ASC to it, it said "using filesort" even though I already altered my table to return in the column order to begin with. Why is it still showing "using filesort" when adding the order by Also, why would it use filesort even though I indexed the column. It is the first column in a 2 column index. Help
VIEWS ON THIS POST

88

Posted on:

Wednesday 24th October 2012
View Replies!

Storing date/time for sorting

I have to do frequent sorting of records based on the timestap. I decided to use integer field for date/time instead of mysql's default DATETIME data type. Also, I want to protect readability of the date/time data, if somebody scans through it. Am I doing it rightly Or, I should drop this idea Please check for `added_on` field. The DATETIME requires at least 19 characters. But integer is even less than that. Being the numbers instead of mixed data, I believe integer field is good. Code: added_on > DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' added_on > INT(10) UNSIGNED NOT NULL DEFAULT 0 Further, the number and datetime are convertible. Code: SELECT CURRENT_TIMESTAMP(); # 2011-08-03 10:01:03 SELECT UNIX_TIMESTAMP(); # 1312344976 SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP())); # 2011-08-03 10:01:27 What can be the performance impact of using numeric field to store datetime data , Originally Posted by bimalpoudel I decided to use integer field for date/time instead of mysql's default DATETIME data type. Why Also, I want to protect readability of the date/time data, if somebody scans through it. Somebody Who Protection of the database has nothing to do with choosing a date type or integer type column. If they can access your database, you've got other things to worry about. Am I doing it rightly Or, I should drop this idea No. Drop the idea The datetime data types exist for a reason. Take advantage of them.
VIEWS ON THIS POST

162

Posted on:

Thursday 25th October 2012
View Replies!

How to sort these records?

Hi! I am building a file/folder tree. I have id, name, parentid, foldertype; 1, root, 0, 1; 2, folderA, 1, 1; 3, folderB, 1, 1; 4, fileA, 2, 0; 5, fileB, 2, 0; Foldertype =1 is folder, 0 is file. Because fileA and fileB belongs to folderA which is parentid = 2, I want to have it appear immediately after folder A. ie. I want to have something like this: 1, root, 0, 1; 2, folderA, 1, 1; 4, fileA, 2, 0; 5, fileB, 2, 0; 3, folderB, 1, 1; Is it possible How
VIEWS ON THIS POST

91

Posted on:

Thursday 25th October 2012
View Replies!

Randomize results then sort

Say I am querying a MySQL database of profiles under a certain city but I want those results randomized and then from that randomized order, sort by user level (admin, general, etc). I have looked at multiple solutions online with ORDER BY and GROUP BY but whenever an ORDER BY RAND() query happens followed by GROUP BY user_level, it doesn't keep the admins at the top. So in a nutshell I am looking to randomize all profiles from table but put the admins in the result first but randomize the admins at the top in addition to randomizing the general users at the bottom. Is this possible I am using PHP by the way.
VIEWS ON THIS POST

102

Posted on:

Thursday 25th October 2012
View Replies!

Retrieve 2 columns & sort

, I was wondering if there is anyway to retrieve 2 seperate columns from a database and combine then into one. Names 1. Doug 2. Fred 3 Michael Companies 1. Microsoft 2. Apple 3. Best Buy So by combining those would actually return entries from 1 to 6. Combined columns 1. Doug 2. Fred 3 Michael 4. Microsoft 5. Apple 6. Best Buy My final question is, is there a way to make sure the values are unique So if for some reason Microsoft was on there twice, it would only pull one of the values and omit the other.
VIEWS ON THIS POST

101

Posted on:

Thursday 25th October 2012
View Replies!

sorting question

, I'll simplify my table to make this easier to understand: I have a table with names and a sort_order. When selecting the names, they are sorted by sort_order. So, for example: (name, sort_order) a, 26 b, 30 c, 11 d, 22 would be sorted as b, a, d, c Because rows can be deleted and inserted by users, the sort_orders won't be consecutive. I'm looking to write a good solution to a move_row_up() and move_row_down() functions, so that I can let a user determine the sort order by clicking buttons. I have written these functions, but I don't like the solution because it involves lots of UPDATEs. My method for move_row_up() involves selecting all the names sorted in sort_order. Then I run through them and add 1 to each row until I get to the row I want to move up, and add 1 to this as well. Then I step back one, and remove the 1 I just added to the the row above the row I want to move up. This algorithm works, but it's clearly pretty poor. The move_row_down() works in the same way. If the row's all had strict consecutive sort_orders..e.g. if I had 10 rows, then the sort_orders would be 1,2,3...,9,10 ... It would be easy to do then. For example, If you wanted to move row 5 up, just swap it with row 4. but users can delete any rows, so they might delete row 4, meaning row 5 can't be moved up. Any ideas on this one Many
VIEWS ON THIS POST

96

Posted on:

Thursday 25th October 2012
View Replies!

sorted list of cities in countries (was "What is the best way to do this?")

, I have database with cities, countries, regions etc. I need to take list of countries from one table, then I need to take list of cities of each country from previous list and sort ALL cities of all countries by some value. What is the best (easiest, fastest) way to do that
VIEWS ON THIS POST

200

Posted on:

Thursday 25th October 2012
View Replies!

sorting DATE field by year

I've have a field ('gamedate') in a table ('games') that is in DATE format, but what I can't figure out is how to use the year as part of a search form option. I'm hoping to get a drop down menu that is a list of years, that when selected will bring back just the records from that year, or in the future, use BETWEEN to find all records from a set group of dates. Is there a step I'm missing to "extract" the "YYYY" part of the DATE field before using it Or maybe there's a wildcard that needs to be stated to get "MM" and "DD" to equal "any"
VIEWS ON THIS POST

96

Posted on:

Thursday 25th October 2012
View Replies!

Basic "sort column" command isn't working

This should be easy for regular SQL users: I'm trying to alter a one column table so that its values are in order and there are no duplicates. What I'm having trouble with is making the change permanent. I'm using INSERT INTO and trying to insert the sorted column into itself: Code: #Table: ct_gamertags #Column: gamertag_id INSERT INTO ct_gamertags (gamertag_id) SELECT DISTINCT gamertag_id FROM ct_gamertags ORDER BY gamertag_id ASC; The new column is shorter than the old one. Even so, the command should do something, or it should give an error, but absolutely nothing happens. What's going wrong
VIEWS ON THIS POST

92

Posted on:

Monday 29th October 2012
View Replies!

What sort of join do I need?

I have a query to select info from two tables, one is a list of music the other is details about the artist related to each of the songs: Code: SELECT music.trackid, music.genre, music.trackname, music.artistid, music.albumname, artists.bandname, music.downloadcount FROM artists LEFT JOIN music ON music.artistid = artists.userid That works fine, but I want to add something extra to remove tracks of the artists that have been banned but this informtion is in another table called "accounts". I basically just want to remove the rows if accounts.accounttype = 0 (i.e. I want the rows where accounts.accounttype != 0). The accounts table has a userid column which relates to the artists userid column. I've tried a few different ways but it doesn't end well. Does anyone know what I should do
VIEWS ON THIS POST

136

Posted on:

Monday 29th October 2012
View Replies!

How would I do this with SQL? Some sort of join maybe?

Say you have the table Relationships (celeb1, celeb2, started, ended) Where celeb1 and celeb2 are two names of celebrities that have had a relationship with each other. celeb1 and celeb2 could be ordered either guy/girl or girl/guy Find the celebs that have been in relationship with the same celeb. The result should be (celeb1, celeb2, celeb3) triples, meaning that celeb1 and celeb2 have been in relationship with celeb3. Basically we need the triples of celebs that have been in a relationship with the same celeb, aka Celeb1, Celeb2, Celeb3 Brad Pitt, John Mayer, Jennifer Aniston or Angelina Jolie, Jennifer Aniston, Brad Pitt
VIEWS ON THIS POST

116

Posted on:

Monday 29th October 2012
View Replies!

Order by random then sort

Hi I have this query: SELECT * FROM softwares group by type order by random(), type ASC unfortunately, it does not sort by type. say my table has: Quote: name, type A 1 B 1 C 2 D 2 it would randomly show 2 software but it will not sort by type, it could be the result a type 2 lists first then followed by type 1, next result would be vice versa. My goal is, to group the result set by type so i only get 1 software of each type, randomly and then sort it by type.
VIEWS ON THIS POST

82

Posted on:

Monday 29th October 2012
View Replies!

Any way to sort CHAR field as number?

I have a column within one of my database tables that is set up as a CHAR(7) data type. The majority of values entered into that field are purely numbers (7 digits between 0 and 9). However, there are a few that include alpha characters (which is one of the reasons I can't set the data type to a number type). When information is entered into the database, unfortunately, the script is recognizing that the input is a number and is inserting it as such (without quotes around it). That means that the information is trimming any leading zeros from the start of the number. For instance, let's say that the first three rows should contain the following information in that column: 1234567 0015832 aaa1682 The problem is that the column is actually storing these values: 1234567 15832 (since the first two characters of this value are 15, it is being placed after 12... in the order - though the first two characters of this value should be 00, instead) aaa1682 Then, when I retrieve the values from the database using this column as my "ORDER BY", the values are being retrieved in the order shown above, rather than sorting them this way: 15832 (since this number is smaller than the number shown below, and will be left-zerofilled by the script when the information is retrieved) 1234567 aaa1682 Is there any way I can adjust the "collation" property of that particular column so that it sorts them correctly Or, is there any way I can specify that I want a CHAR column to be zerofilled on the left Any help would be greatly appreciated. The script that inserts information into this particular database is a general script that interacts with a lot of different databases, so there is no real way to adjust it specifically to always insert this column as a string, no matter what the input is. Thank you.
VIEWS ON THIS POST

99

Posted on:

Monday 29th October 2012
View Replies!

Allow user to sort DB records manually

Hi , I'd need to allow users to manually sort data in the table using move up/move down columns, however, couldn't find or think of any effective algorithm. Any ideas, pls UPDATE: Nevermind, I got it I gues, there will be a 'sorting' column (integer type, for integer PKs same type as PK), and every record has assigned one sorting number going one by one (so for records that follow each other first has x and the other x + 1). When moving, it is basically to swap sorting number for neighbour columns. Some special cases for moving first record in sorting order up or last record down must be taken care of but it is no big deal.
VIEWS ON THIS POST

115

Posted on:

Monday 29th October 2012
View Replies!

Is there a way to sort a strangely formated date field?

I have a field which is a VARCHAR(255) field that contains dates formated as such: MM/YYYY Now when I tell it to sort a query using that date field, it doesnt sort correctly because for example 11/2007 > 03/2006 is > 02/2007. Is there a select query where I can have this field sort correctly by somehow formating the info into a date format understood by mysql
VIEWS ON THIS POST

106

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Select statement not sorting

The missed votes table holds the votes that were missed (a total number) when a legislator comes in mid-session or it holds the total number of votes that had been possible for a legislator who leaves mid-session. There will only ever be one entry in this table for any given legislator for any given session with the data stripped at the end of each legislative session. The chances of a legislator coming back after they leave is microscopically small. The only exception is Gabby Gifford who got shot and we will tally the votes she missed and add that to the table for her calculations when she comes back to congress full time. So in the missed votes table I have three records. Jean Harmon left in May. She was in the legislature while five votes were taken. Heller went from the house to the senate mid-session. He has two records but they are attached to two separate IDs, one for the house (must retain his voting record there) and one for the senate. Is that a more adequate explanation of what's going on When I have a legislator who is no longer active, I need to be able to calculate the percentage of votes they cast (yea or nay) off an accurate base of votes. To do that I calculate how many votes the house has taken (count the distinct IDs in the Votes table) and divide that into the number of yea or nay votes for the legislator after replacing (Total) or subtracting (Subtract) the votes in the missed votes table. If I can't make this work in a single select string I can break it up, push stuff into arrays and do my calculating from there. I'm trying to make this an opportunity to improve my knowledge and skill on more complex select statements. HeadElf
VIEWS ON THIS POST

95

Posted on:

Monday 29th October 2012
View Replies!