retrieve a table of genes for mouse

Question

[Eliran Turgeman]

I need to store locally a table that contains 3 columns - gene symbol, refseq id, ensembl id. (mouse)
I have been able to retrieve such table for human from this source but I'm unable to find a similiar source for mouse.
Ideally I will have 3 copies of the file when each copy is sorted by a different column.

I have been trying to use queries like the following:

mysql --user=genome -N --host=genome-mysql.cse.ucsc.edu -A -D mm10 -e "select name,name2 from refGene" > Refseq2Gene.txt

But that's ofc only gets me two of the three columns I need.
Would appriciate a simple solution - like a modification to the current query I'm using.

Answer 1

[microfuge]

The UCSC table browser can be used directly

• in UCSC mm10 genome
• Tools -> table browser -> group (genes and gene predictions) -> track (gencode VM23)
• In output format dropdown list (select fields from primary and related tables)
• Click get output
• in mm10 kgxref fields (check radio boxes geneSymbol and refseq)
• in Linked tables -> radio button knownToEnsembl
• Click allow selection from checked tables
• Click radio button to fields in knownToEnsembl table
• Click get output to get the tabular file

For writing an SQL query (The table relationships are given in track description here https://genome.ucsc.edu/cgi-bin/hgTables?db=mm10&hgta_group=genes&hgta_track=knownGene&hgta_table=knownGene&hgta_doSchema=describe+table+schema )

i

[Eliran Turgeman]

Exactly what I was looking for, thank you. Do you know perhaps if I can get the tabular file sorted by each column? or I will have to sort it by myself?

[microfuge]

As far as I know there is no option to sort it from the server side.

[Eliran Turgeman]

I just realized that the table contains ensembl transcript ids (format: enmust..) and I need the ensembl id (format: ensg...), Do you know how to convert between the two? or directly modify some of the checks in the UCSC table browser?

[microfuge]

I find the UCSC tables a bit confusing. An alternate and easier (I think) way would be to use ensembl biomart https://www.ensembl.org/biomart/martview/ . You can select database -> ensembl genes . Dataset -> human/mouse genes. Then clicking attributes (on the left pane) -> External . There are radio boxes for different id types.